Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
freyadudding
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.
8 REPLIES 8
Syndicate_Admin
Administrator
Administrator

I also have the same problem, I have my table organized with some categories, from the most general to the most specific. I need to add up weighted values of the products within the categories, but I don't know how to do that. You managed to solve your problem, if so, could you help me?

harshnathani
Community Champion
Community Champion

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)

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. 

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

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?

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

Hey @freyadudding I know this is from 3 years ago, but were able to solve this? I am trying to do the same exact thing you described here and haven´t found a solution yet.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.