Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Running total with ALL() is affected by visual filter

Dear power bi community,   I have a weird problem. Trying to write a calculated measure to calculate a running total by category. The calculation is working so far, however I have problems when usi...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    If your just want the four slicers (filters) on the page are affected, please try formula as below:

     

    RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER (
            ALL ( table ),
            table[slicer_1] = MAX ( table[slicer_1] )
                && table[slicer_2] = MAX ( table[slicer_2] )
                && table[slicer_3] = MAX ( table[slicer_3] )
                && table[slicer_4] = MAX ( table[slicer_4] )
                && table[category] <= MaxCategory
        )
    )

     

    Best Regards,
    Community Support Team _ Eason

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

Try:

RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER ( ALL ( table ), table[category] <= MaxCategory )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you very much PaulDBrown, I think we get closer to the solution, that gives me hope. I did it exactly you described, and checked if the calculation changes, when applying a visual filter and see the calculation is still the same and it is correct. But now I have a nother problem. My four slicers (filters) on the page are affected when using ALL() on the whole table. So the calculation is correct, but the predifined slicer values are missing, so the calculation is not that I want to have. Is there any way, to tell the measure like: Do ALL, but keep the necessary slicers values?
    Thank you in advance!

    EDIT:

     

    Have tried following, but calculation changes when using table[category] as visual filter.

     

    RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER ( ALLEXCEPT ( table, table[slicer_1], table[slicer_2], table[slicer_3], table[slicer_4] ), table[category] <= MaxCategory )
    )
    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Try using ALL(Table[Category])... instead of ALL(Table)....

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks PaulDBrown, unfortuanely the calculation is still changing, when filtering some categories out with visual filter...

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, Anonymous 

      If your just want the four slicers (filters) on the page are affected, please try formula as below:

       

      RunningTotal =
VAR MaxCategory =
    MAX ( table[category] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[issues] ),
        FILTER (
            ALL ( table ),
            table[slicer_1] = MAX ( table[slicer_1] )
                && table[slicer_2] = MAX ( table[slicer_2] )
                && table[slicer_3] = MAX ( table[slicer_3] )
                && table[slicer_4] = MAX ( table[slicer_4] )
                && table[category] <= MaxCategory
        )
    )

       

      Best Regards,
      Community Support Team _ Eason

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi v-easonf-msft  thank you very much, unfortuanely it does still not work.

  • Mr_Jones's avatar
    Mr_Jones
    New Member
    2 years ago

    Thank you PaulDBrown - this really helped me. I adapted your example from DISTINCTCOUNT to SUM for what I needed.