cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keburke
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
parry2k
Super User
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.






Did I answer your question? Mark my post as a solution.

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





View solution in original post

8 REPLIES 8
parry2k
Super User
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.






Did I answer your question? Mark my post as a solution.

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





View solution in original post

keburke
Frequent Visitor

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

parry2k
Super User
Super User

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

 

parry2k_0-1623958550803.png

 

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.

 






Did I answer your question? Mark my post as a solution.

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





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

 

keburke_1-1623959236646.png

 

 

parry2k
Super User
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.






Did I answer your question? Mark my post as a solution.

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





keburke
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

parry2k
Super User
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.






Did I answer your question? Mark my post as a solution.

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





keburke
Frequent Visitor

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors