Forum Discussion
Cumulative as a calculated column based on ranking
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:
The desired result:
M=Margin / C=Cumulative
My data souce:
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.
12 Replies
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.
Anonymous 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.
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.
- rajendraongole1
Super User
Hi Anonymous 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
Thanks rajendraongole1!
Can you share me a screenshot because I don't see any chart on your file.
- rajendraongole1
Hi Anonymous - 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.
