Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Matrix Grouping

Hi, how do you do a percentage off of two fields in the matrix grouping?
In this example, you see the totals here and I'm looking to do a [data changes] / [assessments administered] but every formula I've tried, including a simple [data changes] / [assessments administered]  gives me incorrect results.

How would I go about creating a formula to show % (it seems so simple to do but wow, what a challenge it's been!)

In my grouping the following:
Row: User Name
Columns: Assessments Type
Values: Assessments Administered
            Data Changes


 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

    Based on what I've gotten, it seems to add up all the calculated percentages to get the total.

    Please try using a measure like this:

    Measure =
DIVIDE ( SUM ( [Data Changes] ), SUM ( [Assessments Administered] ) )

     

    If it doesn't work, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    Based on what I've gotten, it seems to add up all the calculated percentages to get the total.

    Please try using a measure like this:

    Measure =
DIVIDE ( SUM ( [Data Changes] ), SUM ( [Assessments Administered] ) )

     

    If it doesn't work, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks so much  _ Zeon Zheng! It was the answer to my question and because of it i was able to finally take my complete my report and hand it over to validation!  You are my hero!

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    if these are measures

    This should world

     

    divide( [data changes] ,[assessments administered])

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak. thanks for your help so far. I tried to use the divide command but it didn't work.  
      Maybe here's a better example.

      When I use a drilldown, the calculation works great on the detail, but in the totals in the same calculation, the result is different
      For example, the calculation is simple here :Data Changes/Assessments Administered
      when drilling down through alice rater, you see the % detail correctly under alice.rater,but when you look at the top data point you see the calculation is incorrect.

      So my question is, why is the aggregation wrong and how would I fix that?  I think that is where the solution lies.