Forum Discussion
Cumulative as a calculated column based on ranking
Hi Anonymous
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.
Hello Anonymous ,
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.
Hi Anonymous ,
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.
Hi Anonymous ,
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.
Hi Anonymous ,
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.
Thanks Anonymous,
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...
Hello Anonymous ,
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.