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!

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