Forum Discussion

ZLS's avatar
ZLS
Frequent Visitor
4 years ago

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.

 

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

 

 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      ZLS
      Frequent Visitor
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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