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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ZLS
Frequent Visitor

Calculate Running total with multiple subcategories and multiple filters

Hi everyone,

I'm running into an issue about Running total. which I would like to calculate the Running total with three subcategories and it can be filtered by each subcategory, also to be able to show the whole Running total number. 

 

So far, I can only search info that only includes two subcategories, I tried to find the solution but it just doesn't work. So, I hope any of you can help me with this issue.

 

So, Here is my screenshot.

- The subcategories are 1) function, 2) Dept name, and 3) Project name. This project is running in two depts at the same time. This running total is not correct, which should be 156,6 (based on the excel calculation), it counts double here.

function base.PNG

 

If I break it down to the dept level, then the Running total is still not correct.

dept base.PNG     2.PNG

This is the formula that I'm using:

Capture.PNG

Running total = CALCULATE(
SUM(Query1[Sales]),
FILTER(all(Query1),
Query1[Month]<=EARLIER(Query1[Month]) && Query1[Function]=EARLIER(Query1[Function]) && Query1[Dept Name]=EARLIER(Query1[Dept Name]) && Query1[Project Name]=EARLIER(Query1[Project Name])))
 
Any comments and help are highly appreciated!!!

 

 

5 REPLIES 5
Anonymous
Not applicable

Hi @ZLS ,

 

//The subcategories are 1) function, 2) Dept name, and 3) Project name. This project is running in two depts at the same time. This running total is not correct, which should be 156,6 (based on the excel calculation), it counts double here.

 

How do I get the result of 156,6? Is it a clerical error, the result should be 111.36? 

If not, please tell me how to calculate it. Thanks in advance.

Looking forward to your reply.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

ZLS
Frequent Visitor

Hey Gao,

Thanks for your reply. Yes, you were right, it is 111,36. Thanks for pointing this out, and can you help me with my question?

Best regards!

ZLS

Anonymous
Not applicable

Hi @ZLS ,

 

Please try this measure.

Running total1 = 
VAR _value1 =
    SELECTEDVALUE ( Query1[Function] )
VAR _value2 =
    SELECTEDVALUE ( Query1[Dept Name] )
VAR _value3 =
    SELECTEDVALUE ( Query1[Project Name] )
VAR _result1 =
    CALCULATE (
        SUM ( Query1[Sales] ),
        FILTER (
            ALL ( Query1 ),
            Query1[Month] <= MAX ( Query1[Month] )
                && Query1[Function] = MAX ( Query1[Function] )
        )
    )
VAR _result2 =
    CALCULATE (
        SUM ( Query1[Sales] ),
        FILTER (
            ALL ( Query1 ),
            Query1[Month] <= MAX ( Query1[Month] )
                && Query1[Function] = MAX ( Query1[Function] )
                && Query1[Dept Name] = MAX ( Query1[Dept Name] )
        )
    )
VAR _result3 =
    CALCULATE (
        SUM ( Query1[Sales] ),
        FILTER (
            ALL ( Query1 ),
            Query1[Month] <= MAX ( Query1[Month] )
                && Query1[Function] = MAX ( Query1[Function] )
                && Query1[Dept Name] = MAX ( Query1[Dept Name] )
                && Query1[Project Name] = MAX ( Query1[Project Name] )
        )
    )
VAR _result =
    SWITCH (
        TRUE (),
        _value1 <> BLANK ()
            && _value2 = BLANK ()
            && _value3 <> BLANK (), _result1,
        _value1 <> BLANK ()
            && _value2 <> BLANK ()
            && _value3 <> BLANK (), _result2,
        _result3
    )
RETURN
    _result

vcgaomsft_0-1656494926986.png

vcgaomsft_1-1656494954812.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

ZLS
Frequent Visitor

Hi Gao,

I really appreciate your help. I have tried using my data set, but I'm still having some problems. The overall Running Total is accurate, but if I wish to filter on a dept level or/and project level to see their Running Total, a problem arises.

It appears that if the dept or project is listed first, there won't be any issues, however, if it is listed 2nd or 3rd, there is an issue with the number. I have attached a few screenshots for your information. Please let me know if I addressed myself clearly or if you need more information! 🙂

1.PNG

 

2.PNG

 

This is where the issue comes3.PNG

 

4.PNG

 

5.PNG

Once again, thank you so much again for your help, and could you please help me with this issue to see if it can be resolved?
Have a nice weekend!

Anonymous
Not applicable

Hi @ZLS ,

 

Please try this.

Running total1 = 
VAR _value1 =
    SELECTEDVALUE ( Query1[Function] )
VAR _value2 =
    SELECTEDVALUE ( Query1[Dept Name] )
VAR _value3 =
    SELECTEDVALUE ( Query1[Project Name] )
VAR _result1 =
    CALCULATE (
        SUM ( Query1[Sales] ),
        FILTER (
            ALL ( Query1 ),
            Query1[Month] <= MAX ( Query1[Month] )
                && Query1[Function] = MAX ( Query1[Function] )
        )
    )
VAR _result2 =
    CALCULATE (
        SUM ( Query1[Sales] ),
        FILTER (
            ALL ( Query1 ),
            Query1[Month] <= MAX ( Query1[Month] )
                && Query1[Function] = MAX ( Query1[Function] )
                && Query1[Dept Name] = MAX ( Query1[Dept Name] )
        )
    )
VAR _result3 =
    CALCULATE (
        SUM ( Query1[Sales] ),
        FILTER (
            ALL ( Query1 ),
            Query1[Month] <= MAX ( Query1[Month] )
                && Query1[Function] = MAX ( Query1[Function] )
                && Query1[Dept Name] = MAX ( Query1[Dept Name] )
                && Query1[Project Name] = MAX ( Query1[Project Name] )
        )
    )
VAR _result =
    SWITCH (
        TRUE (),
        _value1 <> BLANK ()
            && _value2 = BLANK ()
            && _value3 = BLANK (), _result1,
        _value1 <> BLANK ()
            && _value2 <> BLANK ()
            && _value3 = BLANK (), _result2,
        _result3
    )
RETURN
    _result

The PBIX file is attached for reference.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.