Forum Discussion
Category-split value divided by a calculated constant
Hi there!
I have a fact table which includes sales values with various different categories which can be split into "Main Sales" and "Other Sales" groupings (i.e. Category 1-3 = "Main", 4-6 = "Other"). Each entry has "Quantity" and "Value"
The client wants to have the "Other Sales" values split by categories, but all divided by the quantity which is the sum of "Main" sales.
Value of Other Sales (by Category) / SUM("Quantity" of "Main Sales").
This then needs to be provided with a weekly slicer and a YTD value.
What would be the best way to do this?
Thanks!
Rich
Hi richard_jackson ,
Please check the formula:
Column = var a = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week],'Table'[Category]),'Table'[Main]=0))+0 var b = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Main]=1)) return a/b
5 Replies
- lbendlin
Super User
Sounds like a standard weighting pattern. Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi lbendlin - thanks for that!
Data set
Week Category Main Other Quantity Value 1 A 1 0 2 1 1 B 0 1 3 2 1 B 0 1 4 3 1 C 0 1 5 4 2 A 1 0 6 5 2 B 0 1 7 6 2 C 0 1 8 7 Expected outcome
Parameters
Week=1
OtherCategory=1 (i.e. OtherCategory=1 or "true", MainCategory=0 or "false")
MainQuantity=2 (i.e. the sum of Quantity for any values for that week where MainCategory=1)
Week Category Value Value/MainQuantity 1 B 5 2.5 1 C 4 2 Any guidance would be greatly appreciated!
Thanks!
Rich
- lbendlin
something like this?
You would also have to specify how the totals should be computed.
Hi richard_jackson ,
Please check the formula:
Column = var a = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week],'Table'[Category]),'Table'[Main]=0))+0 var b = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Main]=1)) return a/b
