Search code examples
sqlitepowerbidaxpowerquerydata-cleaning

Is there any DAX query for calculate the ratio of distinct value

enter image description hereI have calculated ratio of distinct values from same col in power bi by using new measure, after calculating it I have put that result in card it is not showing result correctly

I have calculated the ratio of distinct values from same column by using some formulae

// 1
DistinctValuesCount = COUNTROWS(CALCULATETABLE(VALUES('YourTableName'[DistinctValues]))) 

// 2
TotalDistinctValuesCount = COUNTROWS('YourTableName')

// 3
DistinctValuesRatio = DIVIDE([DistinctValuesCount], [TotalDistinctValuesCount])

and at the end of calculation, after I put that result in card, its not giving me any count its giving me the 2.54E-5 what does it mean as image is showing the result of calculated ratio, so where am going wrong ?

Solution

  • I'm assuming you want the percentage of a Blood group.
    Create a new measure with:

    Overall % =
  DIVIDE(
    COUNTROWS('YourTableName'),
    CALCULATE( COUNTROWS('YourTableName'), ALL('YourTableName') )
  )

    Then under Measure tools tab in the ribbon, click on the % and choose how many decimal places you want.

    Now use this in your card. It should show 100% since no Blood Group has been selected. Now click on a row in your Matrix, and the card should update with the % of that Blood Group.