cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Sum a Measure at a Category level

I have the following measure of which I need to calculate at a particular category level before summing for these categories. The measure I need to calculate is

[Savings per Item] * [Savings Volume] * 'General Volume Switch'[General Volume Switch Value]
which calculates the savings volume and savings per item at a Product level then multiplies by the General Volume which is a fixed parameter value. For each line in the table, there is a combination of Product, Region, Date etc. Each product belongs to a subcategory.
 
My issue is that I need to do the calculation for each unqiue combination of Subcategory and Region. Then sum these values if that measure is above 0. I tried using SUMX but this does it for each row in my table instead of at a Subcategory and Region level.
 
Savings by Subcategory = CALCULATE(SUMX('Client Table',[Savings per Item] * [Savings Volume] * 'General Volume Switch'[General Volume Switch Value]),FILTER('Client Table','Savings Measures'[Savings per Item]>0))
 
I thought about using the SUMMARIZE formula but wasn't sure how to do this for a measure. I can provide more information if needed but this data is sensitive so would need to annonomise this before sending.
6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Sum a Measure at a Category level

@freyadudding , see allexcept can help

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Sum a Measure at a Category level

Hi @amitchandak thank you very much for taking the time to reply,

The ALLEXCEPT works to calculate my measure at the correct level. But then how do I SUM that measure for each of those results? 

 
Savings by Subcategory = CALCULATE([Savings per Item] * [Savings Volume] * 'General Volume Switch'[General Volume Switch Value],ALLEXCEPT(ClientTable,ClientTable[Region],ClientTable[SubCategory]))
 
i.e. once I have this above I then need to sum these values for each Region and Subcategory to get a total value for all Regions and Subcategories for which the value above is greater than zero
Highlighted
Community Champion
Community Champion

Re: Sum a Measure at a Category level

HI @freyadudding ,

 

Try something like this.

 

Savings by Subcategory =
SUMX (
    FILTER (
        vwClientClinisupplies_BIM,
        'Savings Measures'[Savings per Item] > 0
    ),
    [Savings per Item] * [Savings Volume] * 'General Volume Switch'[General Volume Switch Value]
)

 

If it does not work, pls share sample data and expected output.

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Frequent Visitor

Re: Sum a Measure at a Category level

Okay so I will try provide some more context and examples.

 

At the moment I have got to the point where the following table is calculating correctly for savings per Item and Savings volume and Savings by Subcategory at the subcategory level. But Power BI evaluates the Practice calculation for Savings by subcategory by using the Savings per Item and Savings Volume at the practice level (line in the matrix that is blacked out).

 

freyadudding_1-1594125914414.png

My calculation for Savings by Subcategory is now

 
Savings by Subcategory = IF('Savings Measures'[Savings per Item]>0,[Savings per Item] * [Savings Volume] * 'General Volume Switch'[General Volume Switch Value],0)

 

When aggregating up to Practice level or any level above this, I want instead to SUM the Savings by Subcategory column for the subcategories below it. i.e. in this example I want the practice to show the SUM of 0+99.62+177.66+0+0+187.90+41.00 etc... instead of calculating the measure based on the values at the practice level. i.e. at the moment it is doing If -1.23>0 then -1.23*124,503*0.8 else 0

 

('General Volume Switch'[General Volume Switch Value] is a fixed value of 0.8)

 

Let me know if this makes sense, if not will try give a better example. 

Highlighted
Community Champion
Community Champion

Re: Sum a Measure at a Category level

Hi @freyadudding ,

 

You will need to use ISINSCOPE function.

 

Please have a look at this video for Category and Subcategory level calculations

 

https://www.youtube.com/watch?v=sSUCyps_1O4

 

Regards,

Harsh Nathani

Highlighted
Frequent Visitor

Re: Sum a Measure at a Category level

Hi @harshnathani that helped for how to do the hiearchy but did not help me be able to SUM the measure for each of the categories? How can I SUM my measure in the cases where the ISINSCOPE is false?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors