Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm having trouble with how to make aggregations on a cumulative sales percentage measures in other measure.
I've made a measure ('cumulative_percentage') that shows the cumulative percetage for sales of each product. When I add it to a table with the Product variable it shows what I need.
It looks like this:
cumulative_percentage =
VAR CurrentProduct = SUM(financials[ Sales])
VAR SummarizedTable = SUMMARIZE(
ALLSELECTED(financials),
financials[Product],
"product_sales", SUM(financials[ Sales])
)
VAR CumulativeSum = SUMX(
FILTER(SummarizedTable,[product_sales] >= CurrentProduct), [product_sales]
)
VAR TotalSelectedProducts = CALCULATE(SUM(financials[ Sales]), ALLSELECTED(financials))
VAR cumulative_percentage =
DIVIDE(
CumulativeSum,
TotalSelectedProducts,
0
) * 100
Return(cumulative_percentage)
But I need to make a Card visual that shows me the number of Products where the corresponding cumulative percentage is above 50% in the current filter context.
I have tried a bunch of different things, and I keep running my head into a wall. It needs to be subject to filters, so it needs to be done in measures.
THIS article looks like the right sort of thing, and I've tried to implement it, but as far as I could tell it references a ranking column, which I don't have and have been unable to make on the fly within measure.
Any help would be greatly appreciated 🙏
MWE available at https://ufile.io/hb05wj7j
Solved! Go to Solution.
I build this solution on the Adventure Works DW 2020. I hope it will help.
#prod =
VAR _tableWithSales =
ADDCOLUMNS (
ALLSELECTED ( 'Product'[Product] ),
"@sales", [Total Sales Amount]
)
VAR _tableWithCum =
ADDCOLUMNS (
_tableWithSales,
"@**bleep**",
VAR _current = [@sales]
RETURN
DIVIDE (
SUMX ( FILTER ( _tableWithSales, [@sales] <= _current ), [@sales] ),
SUMX ( _tableWithSales, [@sales] )
)
)
RETURN
COUNTROWS ( FILTER ( _tableWithCum, [@**bleep**] > 0.5 ) )
Thank you so much, JW! Works like a charm 🙂
I build this solution on the Adventure Works DW 2020. I hope it will help.
#prod =
VAR _tableWithSales =
ADDCOLUMNS (
ALLSELECTED ( 'Product'[Product] ),
"@sales", [Total Sales Amount]
)
VAR _tableWithCum =
ADDCOLUMNS (
_tableWithSales,
"@**bleep**",
VAR _current = [@sales]
RETURN
DIVIDE (
SUMX ( FILTER ( _tableWithSales, [@sales] <= _current ), [@sales] ),
SUMX ( _tableWithSales, [@sales] )
)
)
RETURN
COUNTROWS ( FILTER ( _tableWithCum, [@**bleep**] > 0.5 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |