Run Calculation on one group level and sum on another group level

[Anonymous]

I have a table that I need to run a calculation by grouping values on one level, but then when they are grouped on another level they need to be added together. I also need to make sure the values are still filterable by the date. A sample of the data would be as follows:

Date	Group	Phase	Value 1	Value 2
1/1/20	01	01.01	6	12
4/3/20	01	01.02	8	20
4/5/20	01	01.02	4	28
6/8/20	01	01.01	12	36

So what I need to do is run a calculation on a phase level, say Divide(Sum(Value1),Sum(Value2). 

For Phase 01.01 I would expect: Divide(Sum(6+12),Sum(12+36)) to give me .375.

For Phase 01.02 I would expect: Divide(Sum(8+4),Sum(28+20)) to give me .25.

On the group level I would want these to add to .625.

However I can only get a calculation to give me Divide(Sum(6+12+8+4),Sum(12+36+28+20)), which is .3125.

The only other way I have been able to get my expected result is to place the values in another table but without date.

Is there a way to do this where I can still filter by date?

[parry2k · Accepted solution]

@Anonymous try this:

Sum Measure = 
SUMX (
   SUMMARIZE ( Table, Table[Group], Table[Phase] ),
   DIVIDE (
        CALCULATE ( SUM ( Table[Value1] ),
        CALCULATE ( SUM ( Table[Value2] )
   )
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

This seems to work! Hopefully I can get this to work in my actual dataset 🙂

[parry2k]

@Anonymous not sure why it is not working for you, but it works fine based on the solution I provided:

Share your dax measure? 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Ah I realize that my table was formatted really poorly in the first post, here's a screen shot that will hopefully provide more clarity.

[parry2k]

@Anonymous sounds good. Once you are happy, accept a solution so that it can help others as well. 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Unfortunately it looks my results are not summing the way I would expect, that calculation is giving me Divide(Sum(6+12+8+4),Sum(12+36+28+20)) =.3125 versus what I would expect Divide(Sum(6+12),Sum(12+36)) and Divide(Sum(8+4),Sum(28+20)) = .625

[parry2k]

@Anonymous try this

Sum Measure = 
SUMX (
   VALUES ( Table[GroupPhase] ),
   DIVIDE (
        CALCULATE ( SUM ( Table[Value1] ),
        CALCULATE ( SUM ( Table[Value2] )
   )
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I think this is working so far! I haven't gone through all I need but so far it is promising!