Running Total with filters from different tables

[gpillai99]

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!

[gpillai99]

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.

[Fowmy]

@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.

[gpillai99]

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!

[v-xiaotang]

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.

[Fowmy]

@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