Forum Discussion

msemafor's avatar
msemafor
Frequent Visitor
1 year ago
Solved

Conditional summing in measure or calculated column

Hi,

Have a tricky issue with measure and/or calculated column.

In a matrix need to put 6 categories but 2 of then have to be extra calculated:

1. Dziennie[Grupa towarowa]"Handlowe"+Dziennie[Przyporzadkowanie do grup]"Totalizator sportowy"

2. Dziennie[Grupa towarowa]"Listowe"-Dziennie[Grupa towarowa]"Filatelistyka"

Thought about conditional code and here is my dax:

MyMeasure = SWITCH(TRUE(),

SELECTEDVALUE(Dziennie[Grupa towarowa])="Handlowe",
CALCULATE(SUMX(Dziennie,[kwota brutto]+[Kwota]),FILTER(ALL(Dziennie),Dziennie[Grupa towarowa]="Handlowe" || Dziennie[Przyporządkowanie do grupy]="Totalizator sportowy")),

SELECTEDVALUE(Dziennie[Grupa towarowa])="Listowe",
CALCULATE(SUMX(Dziennie,[kwota brutto]+[Kwota]),FILTER(ALL(Dziennie),Dziennie[Grupa towarowa]="Listowe"))-CALCULATE(SUMX(Dziennie,[kwota brutto]+[Kwota]),FILTER(ALL(Dziennie),Dziennie[Grupa towarowa]="Filatelistyka")),

SUMX(Dziennie,[kwota brutto]+[Kwota]))

Measure does its job fine except grand total which is bigger than summing date from matrix manually. Calculated column shows some cosmic values.

Can you please help with obtaining right values no matter if in a measure or calculated column? It can even be a custom grand total or custom column in Power Query with apriopriate formula.

Thanks a lot in advance.

  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    1 year ago

    You're trying to create a custom DAX measure in Power BI for a matrix visual. While your measure shows the correct values for each row, the Grand Total at the bottom is incorrect and too high.

    This happens because SELECTEDVALUE() works fine for individual rows, but in the total row, there's no single value to select—so it returns blank. As a result, your SWITCH(TRUE()) formula uses the fallback calculation and sums everything, which leads to an overstated total.

    To fix this, I recommend using either the ISINSCOPE() function or rewriting your logic using SUMX() over the grouped values.

    • Using ISINSCOPE() (handles totals correctly)
      "

      MyFixedMeasure =
      VAR IsHandlowe = ISINSCOPE(Dziennie[Grupa towarowa]) && SELECTEDVALUE(Dziennie[Grupa towarowa]) = "Handlowe"
      VAR IsListowe = ISINSCOPE(Dziennie[Grupa towarowa]) && SELECTEDVALUE(Dziennie[Grupa towarowa]) = "Listowe"

      RETURN
      SWITCH(
      TRUE(),
      IsHandlowe,
      CALCULATE(
      SUMX(Dziennie, [kwota brutto] + [Kwota]),
      Dziennie[Grupa towarowa] = "Handlowe" || Dziennie[Przyporządkowanie do grupy] = "Totalizator sportowy"
      ),
      IsListowe,
      CALCULATE(
      SUMX(Dziennie, [kwota brutto] + [Kwota]),
      Dziennie[Grupa towarowa] = "Listowe"
      ) -
      CALCULATE(
      SUMX(Dziennie, [kwota brutto] + [Kwota]),
      Dziennie[Grupa towarowa] = "Filatelistyka"
      ),
      CALCULATE(SUMX(Dziennie, [kwota brutto] + [Kwota]))
      )

      "
    • If totals are still not accurate:

    "MyTotalSafeMeasure =
    SUMX (
    VALUES(Dziennie[Grupa towarowa]),
    VAR GroupName = Dziennie[Grupa towarowa]
    RETURN
    SWITCH(
    TRUE(),
    GroupName = "Handlowe",
    CALCULATE(
    SUMX(Dziennie, [kwota brutto] + [Kwota]),
    Dziennie[Grupa towarowa] = "Handlowe" || Dziennie[Przyporządkowanie do grupy] = "Totalizator sportowy"
    ),
    GroupName = "Listowe",
    CALCULATE(
    SUMX(Dziennie, [kwota brutto] + [Kwota]),
    Dziennie[Grupa towarowa] = "Listowe"
    ) -
    CALCULATE(
    SUMX(Dziennie, [kwota brutto] + [Kwota]),
    Dziennie[Grupa towarowa] = "Filatelistyka"
    ),
    CALCULATE(SUMX(Dziennie, [kwota brutto] + [Kwota]))
    )
    )
    "

     

     

9 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi msemafor please check this

     

    create a disconnected table lke this

     

    CategoryTable = DATATABLE(
        "CustomCategory", STRING,
        {
            {"Handlowe + Totalizator sportowy"},
            {"Listowe - Filatelistyka"},
            {"Handlowe"},
            {"Listowe"},
            {"Filatelistyka"},
            {"Elektronika"},
            {"Spozywcze"}
        }
    )
     
     
    and then the measure as this
     
    CustomMeasure =
    VAR SelectedCategory = SELECTEDVALUE(CategoryTable[CustomCategory])

    RETURN
    SWITCH(TRUE(),
        SelectedCategory = "Handlowe + Totalizator sportowy",
            CALCULATE(SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                Dziennie[Grupa towarowa] = "Handlowe"
                || Dziennie[Przyporzadkowanie do grup] = "Totalizator sportowy"
            ),

        SelectedCategory = "Listowe - Filatelistyka",
            CALCULATE(SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                Dziennie[Grupa towarowa] = "Listowe"
            )
            -
            CALCULATE(SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                Dziennie[Grupa towarowa] = "Filatelistyka"
            ),

        CALCULATE(SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
            Dziennie[Grupa towarowa] = SelectedCategory
        )
    )
    • msemafor's avatar
      msemafor
      Frequent Visitor
      1 year ago

      techies thanks a lot for your response.

      Categories values are fine but grand total is empty although it's turned on in visual settings.

       Need it so it has to be in my matrix.

      • techies's avatar
        techies
        Super User
        1 year ago

        Hi msemafor please try this

         

        CustomMeasure 2 =
        VAR SelectedCategory = SELECTEDVALUE(CategoryTable[CustomCategory])

        VAR CalcForCategory =
            SWITCH(
                SelectedCategory,
                "Handlowe + Totalizator sportowy",
                    CALCULATE(
                        SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                        Dziennie[Grupa towarowa] = "Handlowe"
                            || Dziennie[Przyporzadkowanie do grup] = "Totalizator sportowy"
                    ),

                "Listowe - Filatelistyka",
                    CALCULATE(
                        SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                        Dziennie[Grupa towarowa] = "Listowe"
                    )
                    -
                    CALCULATE(
                        SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                        Dziennie[Grupa towarowa] = "Filatelistyka"
                    ),

               
                CALCULATE(
                    SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                    Dziennie[Grupa towarowa] = SelectedCategory
                )
            )

        RETURN
        IF(
            NOT ISBLANK(SelectedCategory),
            CalcForCategory,
            SUMX(
                CategoryTable,
                VAR ThisCategory = CategoryTable[CustomCategory]
                RETURN
                    SWITCH(
                        TRUE(),
                        ThisCategory = "Handlowe + Totalizator sportowy",
                            CALCULATE(
                                SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                                Dziennie[Grupa towarowa] = "Handlowe"
                                    || Dziennie[Przyporzadkowanie do grup] = "Totalizator sportowy"
                            ),

                        ThisCategory = "Listowe - Filatelistyka",
                            CALCULATE(
                                SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                                Dziennie[Grupa towarowa] = "Listowe"
                            )
                            -
                            CALCULATE(
                                SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                                Dziennie[Grupa towarowa] = "Filatelistyka"
                            ),

                   
                        CALCULATE(
                            SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
                            Dziennie[Grupa towarowa] = ThisCategory
                        )
                    )
            )
        )
         
         

         

  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    Super User
    1 year ago

    You're trying to create a custom DAX measure in Power BI for a matrix visual. While your measure shows the correct values for each row, the Grand Total at the bottom is incorrect and too high.

    This happens because SELECTEDVALUE() works fine for individual rows, but in the total row, there's no single value to select—so it returns blank. As a result, your SWITCH(TRUE()) formula uses the fallback calculation and sums everything, which leads to an overstated total.

    To fix this, I recommend using either the ISINSCOPE() function or rewriting your logic using SUMX() over the grouped values.

    • Using ISINSCOPE() (handles totals correctly)
      "

      MyFixedMeasure =
      VAR IsHandlowe = ISINSCOPE(Dziennie[Grupa towarowa]) && SELECTEDVALUE(Dziennie[Grupa towarowa]) = "Handlowe"
      VAR IsListowe = ISINSCOPE(Dziennie[Grupa towarowa]) && SELECTEDVALUE(Dziennie[Grupa towarowa]) = "Listowe"

      RETURN
      SWITCH(
      TRUE(),
      IsHandlowe,
      CALCULATE(
      SUMX(Dziennie, [kwota brutto] + [Kwota]),
      Dziennie[Grupa towarowa] = "Handlowe" || Dziennie[Przyporządkowanie do grupy] = "Totalizator sportowy"
      ),
      IsListowe,
      CALCULATE(
      SUMX(Dziennie, [kwota brutto] + [Kwota]),
      Dziennie[Grupa towarowa] = "Listowe"
      ) -
      CALCULATE(
      SUMX(Dziennie, [kwota brutto] + [Kwota]),
      Dziennie[Grupa towarowa] = "Filatelistyka"
      ),
      CALCULATE(SUMX(Dziennie, [kwota brutto] + [Kwota]))
      )

      "
    • If totals are still not accurate:

    "MyTotalSafeMeasure =
    SUMX (
    VALUES(Dziennie[Grupa towarowa]),
    VAR GroupName = Dziennie[Grupa towarowa]
    RETURN
    SWITCH(
    TRUE(),
    GroupName = "Handlowe",
    CALCULATE(
    SUMX(Dziennie, [kwota brutto] + [Kwota]),
    Dziennie[Grupa towarowa] = "Handlowe" || Dziennie[Przyporządkowanie do grupy] = "Totalizator sportowy"
    ),
    GroupName = "Listowe",
    CALCULATE(
    SUMX(Dziennie, [kwota brutto] + [Kwota]),
    Dziennie[Grupa towarowa] = "Listowe"
    ) -
    CALCULATE(
    SUMX(Dziennie, [kwota brutto] + [Kwota]),
    Dziennie[Grupa towarowa] = "Filatelistyka"
    ),
    CALCULATE(SUMX(Dziennie, [kwota brutto] + [Kwota]))
    )
    )
    "

     

     

    • msemafor's avatar
      msemafor
      Frequent Visitor
      1 year ago

      Thanks a lot Ilgar_Zarbali - your second dax works great in matrix. However it shows values below 0 in ribon chart when all categories in slicer are off [grand total]. When I click one of them it shows correct values.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi msemafor 


    Welcome to the Microsoft Fabric Community Forum. Also, thank you techies and Ilgar_Zarbali for providing your helpful response on this topic. 

     

    Regarding the issue with your color measure not functioning as expected in Power BI. While the exact structure of your dataset wasn't provided, I’ve prepared a sample .pbix file to demonstrate a potential solution based on a typical scenario.

    Update the measure so that it returns BLANK for Filatelistyka row, but still subtracts it in Listowe.

    CorrectedMeasure = 
VAR CurrentCategory = SELECTEDVALUE(Dziennie[Grupa towarowa])

-- Handlowe + Totalizator
VAR Handlowe_Totalizator =
    CALCULATE(
        SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
        FILTER(
            ALL(Dziennie),
            Dziennie[Grupa towarowa] = "Handlowe" || Dziennie[Przyporzadkowanie do grup] = "Totalizator sportowy"
        )
    )

-- Listowe - Filatelistyka
VAR Listowe_minus_Filatelistyka =
    CALCULATE(
        SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
        Dziennie[Grupa towarowa] = "Listowe"
    ) -
    CALCULATE(
        SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]),
        Dziennie[Grupa towarowa] = "Filatelistyka"
    )

-- Final return
RETURN
    SWITCH(
        TRUE(),
        CurrentCategory = "Handlowe", Handlowe_Totalizator,
        CurrentCategory = "Listowe", Listowe_minus_Filatelistyka,
        CurrentCategory = "Filatelistyka", BLANK(),  
        NOT ISINSCOPE(Dziennie[Grupa towarowa]),  
            Handlowe_Totalizator + Listowe_minus_Filatelistyka,
      
        CALCULATE(SUMX(Dziennie, Dziennie[kwota brutto] + Dziennie[Kwota]))
    )


    I have  attached a .PBIX, please take a moment to review them and see if this solution aligns with your requirements.

     If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution. If you have any additional questions or require further assistance with implementing this measure in your visuals, please do not hesitate to follow up here.

     

    If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

     

    Thank you!

     



     

    • v-karpurapud's avatar
      v-karpurapud
      Community Support
      1 year ago

      Hi msemafor 

      As we haven't received a response from you, I'm following up to see if you had a chance to review the solution I provided. If it meets your requirements and resolves the issue, please mark it as the accepted solution to assist others in finding it more easily.

      Thank You!

      • msemafor's avatar
        msemafor
        Frequent Visitor
        1 year ago

        There's still something wrong - it shows same values in each row so can't mark it as solved. But I'm gratefull for helping me - appreciate your will to share knowledge.

        I put second dax created by Ilgar_Zarbali but only into matrix. Wanted also to show data on ribbon chart but his measure shows values below 0 on this chart.