Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I've been struggling to understand what's wrong with my DAX code for Running Total and would appreciate some help from experts in this forum.
I have a matrix with Customer table and two measures - Revenue YTD and Operating Profit. I next wrote a RANX measure, ranking Customers based on Operating Profit. I then attempted to write DAX code for Cumulative OP. The issue is I want to maintain the filters from the Entity table (Business Group Column) and Date table (FY and Mth-Yr) - but could not get it to work. My current code is clearly wrong as it does not sum up the preceding values.
Would really appreciate some help, thanks!
Thanks @Fowmy . I think I understand the syntax you provided. The ALLSELECTED will remove all filters on the Product column, but in my case the filters I have applied -Date and Entity are from different tables from the Customer table. That is why I tried using ALLEXEPT. Any ideas, thanks.
@gpillai99
If you can share a dummy Power BI file similar to your model and explain what you are trying to achieve then it will be easier for me to provide a solution.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks @Fowmy , I appreciate your help. I'm basically trying to calculate Cumulative Totals for the ranked Customer profits without removing the page filters. Some call this a Pareto analysis while others call it a whale curve. The idea is a curve that starts sloping up with the most profitable customers and ends with a downward slope indicating loss making customers. (https://www.bakertilly.com/insights/visualizing-customer-profitability-with-the-whale-curve). In my case the X-axis is simply a list of Customers ranked on highest profit to lowest and Y-axis is the Cumulative Profits. Is the Y axis that I have difficulty calculating.
Here is the pbix file.
https://drive.google.com/drive/folders/1rkowU2ky7DNol8JEJTPQo9f5Yol7sxMp?usp=sharing
Once again thanks in advance!
Hi @gpillai99
Can you describe what results you want to get based on the re-shared sample file? Thanks!
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@gpillai99
You can use a formula as I did with a sample dataset, please check and modify your accordingly. You do not need to use rankx for this. File is attached
Cumm Units =
VAR __UNITS = [Total Units]
VAR RESULT =
CALCULATE(
[Total Units],
FILTER(
ALLSELECTED(financials[Product]),
[Total Units] >= __UNITS
)
)
RETURN
RESULT
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |