Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Sum a Measure at a Category level

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

freyadudding

Frequent Visitor

Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
03:48 AM

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
##

**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

amitchandak

Super User IV

Re: Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
03:58 AM

@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

Proud to be a Super User!

Highlighted
##

freyadudding

Frequent Visitor

Re: Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
04:08 AM

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
##

harshnathani

Community Champion

Re: Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
04:10 AM

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
##

freyadudding

Frequent Visitor

Re: Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
05:54 AM

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

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
##

harshnathani

Community Champion

Re: Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2020
06:20 AM

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
##

freyadudding

Frequent Visitor

Re: Sum a Measure at a Category level

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-09-2020
02:49 AM

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?

Announcements

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

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

Top Solution Authors

User | Count |
---|---|

378 | |

132 | |

96 | |

93 | |

91 |

Top Kudoed Authors

User | Count |
---|---|

484 | |

185 | |

180 | |

139 | |

119 |