CUMULATIVE SUM. BY CATEGORY

[Syndicate_Admin]

Hello. Can you help me please. I have not been able to make this measure.

I have this table:

I have a few price ranges and some brands.

I have a distinctive count on each brand to know how many materials it has.

I need to pull out a cumulative measure that adds up the materials as the price range increases.

The idea is that this measure will serve me for the whole and for %.

Please help me. All the solutions I have found are for dates. But this price range field is a text, not a date.

Thank you.

[v-cgao-msft · Accepted solution]

Hi @Syndicate_Admin ,

You will need to add an [Index] column to the PowerQuery editor to assist in [PUNTO DE PRECIO] sorting.

Sort one column by another column in Power BI

Then please create a new measure.

Measure = 
VAR _cumulative_count =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) ),
        [@MARCA 1]
    )
VAR _total_count =
    SUMX ( ALL ( 'Table' ), [@MARCA 1] )
VAR _result =
    DIVIDE ( _cumulative_count, _total_count )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[miTutorials]

You can use Window() function to achieve this. Hope this detailed tutorial helps.

Cumulative Sales/Running Total for a Category with Window() Function in PowerBI | MiTutorials - YouT...

[Syndicate_Admin]

Hello. Thanks a lot.

Try to do it but don't succeed. fails to extract the accumulated measure in units and %.

try all combinations of 0-1 and ABS-REL but fail. I didn't accumulate.

But try another measure... I share it at the end.

But try two other measures and succeed. I share them:

Part 1 =

COUNTX(FILTER(ALLSELECTED(Consolidated),Consolidated[PRICE RANGE]<=.MAX(Consolidated[PRICE RANGE])),[COUNTER MATERIALS BRANDS])

and for the cumulative %

Cumulative measure =

where _1=COUNTX(FILTER(ALLSELECTED(Consolidated),Consolidated[PRICE RANGE]<=.MAX(Consolidated[PRICE RANGE])),[COUNTER MATERIALS BRANDS])

where _2=COUNTX(ALLSELECTED(Consolidated),[COUNTER MATERIALS BRANDS])

return

DIVIDE(_1,_2)

[PijushRoy]

Hi 

The requirement is not clear. What is the calculation for 6%, 7%, 11% so on
Could you please provide pbix file with sample data and calculation you need.

Thanks 
Pijush