Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gpillai99
Frequent Visitor

Running Total with filters from different tables

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_4-1635674025447.png

 

gpillai99_0-1635673372629.png

gpillai99_2-1635673758173.png

gpillai99_3-1635673794008.png

 

5 REPLIES 5
gpillai99
Frequent Visitor

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.


Did I answer your question? Mark my post as a solution! and hit thumbs up


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!

vxiaotang_0-1636017318969.png

 

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
Super User
Super User

@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

 

Fowmy_0-1635680765697.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.