Conditional summing in measure or calculated column

[msemafor]

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.

[v-karpurapud]

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]

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]

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.

[v-karpurapud]

Hi @msemafor 

If possible can you share the sample data so we can better understand and address your issue?

Regards,
Karpurapu D.

[Ilgar_Zarbali · Accepted solution]

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]

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.

[techies]

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]

@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]

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

                )

            )

    )

)