Counting products based on cumulative sales percentage measures

[PeterSimonsen87]

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

[PeterSimonsen87]

Thank you so much, JW! Works like a charm 🙂

[JW_van_Holst · Accepted 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 ) )