Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count of IDs

Hi Team,

 

I'm trying to achieve the below marked in yellow. I have a table with IDs, and ratings.  I have the same pivot table(shown below) in Powerbi with Matrix visual.  

 

I need a Dax function to calcualte the Satisfactory rating which is the sum of Rating 1&2 / Total Ratings.

 

Kindly help.

 

 

Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous ,

    use the following measure:

     

     

     

    Count of IDs = COUNTROWS('Table')

Satisfactory = 
VAR _Rating1 = CALCULATE([Count of IDs], 'Table'[Rating] = 1)
VAR _Rating2 = CALCULATE([Count of IDs], 'Table'[Rating] = 2)
VAR _Total = CALCULATE([Count of IDs], ALL(DimRating))
RETURN
   DIVIDE(_Rating1 + _Rating2, _Total)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

2 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous ,

    use the following measure:

     

     

     

    Count of IDs = COUNTROWS('Table')

Satisfactory = 
VAR _Rating1 = CALCULATE([Count of IDs], 'Table'[Rating] = 1)
VAR _Rating2 = CALCULATE([Count of IDs], 'Table'[Rating] = 2)
VAR _Total = CALCULATE([Count of IDs], ALL(DimRating))
RETURN
   DIVIDE(_Rating1 + _Rating2, _Total)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    Can you attach a sample data in a file please?

    Just seeeing the screenshot will take osme time to manually put this data in power bi.

     

    Thanks,

    Pragati