Cumulative as a calculated column based on ranking

[Bluemoon07]

Hello Power Bi communauty, 

I am looking to create an interactive dashboard with the above slicers. I want to create a line chart that shows the evolution of margins on the Y-axis and the cumulative sales on the X-axis relative to the margins. The margins are sorted in descending order. I succeeded in using the table and creating measures. However, I cannot place a measure on the X-axis. How create a cumulative column that also change based on my slicer as a measure.

Here are my calculations:

CumulativeMeasure =

SUMX(FILTER(ALLSELECTED('Table'),[RankMeasure] <= MAXX(ALLSELECTED('Table'), [RankMeasure])),'Table'[Sales])

RankMeasure = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Margin])),,DESC,DENSE)

The desired result:

M=Margin / C=Cumulative

My data souce: 

[v-tsaipranay · Accepted solution]

Hi @Bluemoon07 

Thank you for using Microsoft Community Forum.

As mentioned by @rajendraongole1  Power BI does not allow measures on the X-axis directly unless they are used as aggregations over a column.

To resolve your issue, I used your data source as a sample and it worked fine for me. Please review the pbix file I am including for better understanding.

I hope this will reslove your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay · Accepted solution]

Hi @Bluemoon07 

Thank you for using Microsoft Community Forum.

As mentioned by @rajendraongole1  Power BI does not allow measures on the X-axis directly unless they are used as aggregations over a column.

To resolve your issue, I used your data source as a sample and it worked fine for me. Please review the pbix file I am including for better understanding.

I hope this will reslove your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[Bluemoon07]

@v-tsaipranay Thanks! This mostly answers my question. However, I'm not sure if it's because my application is not update... The "cumulationvolume" measure is done over the entire year and the "rankmeasure" does not adapt to my slicers (month; category product)...Can you tell me if it works for you ? If no, I will update it.

Also How I rank magin in descending order, with a filter quality product in descending order too.

[v-tsaipranay · Accepted solution]

Hello @Bluemoon07 ,

Thank you for your follow-up question. Based on your feedback, we have identified two key concerns:

CumulativeVolume is calculated over the entire year, and RankMeasure does not adapt to slicers.

• This issue might happen if the slicers (Month, Category Product) are from a different table than the visualization. In Power BI, slicers only filter tables they are directly connected to. Also slicers from the original table won't filter data from the calculated table unless there's a relationship between the tables.

To resolve this, you can either:

Please use slicers from the calculated table and ensure that all filters are applied within the same dataset. Alternatively, you can create a relationship between the original and calculated table in Model View to enable slicers to filter both tables correctly.

Sorting Margin in descending order while applying a tiebreaker for Quality Product.

• If you want to rank Margin from highest to lowest while ensuring that records with the same Margin are further sorted by Quality Product (also in descending order), the following DAX formula will achieve this:

RankMeasure =

RANKX(

    ALLSELECTED('Table'), 

    CALCULATE(SUM('Table'[Margin])),

    CALCULATE(SUM('Table'[Quality Product])),  -- Acts as a tiebreaker

    DESC, DENSE

)

This ensures that the highest Margin values rank first, and in cases where Margin values are the same, Quality Product is used to determine ranking order.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @Bluemoon07 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Bluemoon07]

Hi @v-tsaipranay ,

Sorry for my last reply,I encountered some issues while updating my PowerBI...

I've checked PowerBI and used slicers from the "CumulativeTable" as suggested. However, I'm still facing the same issue. It seems to be related to the rank measure on "CumulativeTable," which doesn't reorganize the ranking based on the selected month. Consequently, the CumulativeVolume from "CumulativeTable" accumulates volume based on the rank measure.

Please refer to the attached picture. The cumulative volume should be 110.8, but it's showing 484.54 because it accumulates the volume from all rows. How can I rank based on the selected month (with the possibility to select multiple months, like two)? Then, have a cumulative volume based on this new ranking.

[v-tsaipranay]

Hi @Bluemoon07 ,

Thanks for following up! Based on your findings, the issue is that the RankMeasure is still considering the entire dataset instead of dynamically updating per selected month(s). This is why the CumulativeVolume is summing across all months rather than resetting based on selection.

Update RankMeasure to ensure it recalculates based on slicer selections:

RankMeasure =  
RANKX(  
    CALCULATETABLE('Table', ALLSELECTED('Table'[Month])),  
    CALCULATE(SUM('Table'[Margin])),  
    ,DESC, DENSE  
)

Then, adjust CumulativeVolume to follow the updated ranking:

CumulativeVolume =  
SUMX(  
    FILTER(ALLSELECTED('Table'), [RankMeasure] <= MAXX(ALLSELECTED('Table'), [RankMeasure])),  
    'Table'[Volume]  
)

Now, ranking and accumulation will update dynamically per selected month(s). Try it out and let us know if you need further assistance.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[Bluemoon07]

Thanks @v-tsaipranay, 

It works as measure. But not when I used aggregations to have a cumulativevolume as column. Can you review the pbix file to see if it's possible...

[v-tsaipranay]

Hello @Bluemoon07 ,

Thank you for the update.

You're correct that the CumulativeVolume works as a measure but not as a calculated column due to a Power BI design limitation. Calculated columns are evaluated at data refresh time and do not respond to slicers or filters, which is why the column shows cumulative values across the full dataset regardless of slicer selection.

Additionally, if you're computing the column in a separate table (e.g., CumulativeTable), please ensure there's an active relationship between this table and your main data table. Missing relationships can prevent slicer filters from propagating properly.

To achieve dynamic, slicer-responsive cumulative logic, we recommend using measures, as they calculate at query time and respect all filter contexts.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[rajendraongole1]

Hi @Bluemoon07 Power BI does not allow measures on the X-axis directly unless they are used as aggregations over a column

Shared the pbix file FYR:

Please check

[Bluemoon07]

Thanks @rajendraongole1!

Can you share me a screenshot because I don't see any chart on your file. 

[rajendraongole1]

Hi @Bluemoon07  - required calculated column created, can you please try to generate the same in file shared. 

Let me know if the output that you are looking for.

[Bluemoon07]

@rajendraongole1 I check the cumulative Measure from "CumulativeSalesTable" and summmary sales from Table "nput" is different. Also, CumulativeSales from "CumulativeSalesTable" never change based on slicers