cancel
Showing results for
Did you mean:
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
Super User

@keburke 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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

8 REPLIES 8
Super User

@keburke 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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Super User

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

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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.

Super User

@keburke 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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Super User

@keburke 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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.