Calculate Running total with multiple subcategories and multiple filters

[ZLS]

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.

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

     

 

This is the formula that I'm using:

 

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!!!

[Anonymous]

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]

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]

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

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]

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! 🙂

This is where the issue comes

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]

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