Column total is wrong but row sub-totals are right - DAX help

[AltGr9]

I have a much larger version of this really simple dataset:

In my Power BI report, I have a slicer filtering on [Date], and a table visual showing three measures, but the grand total is not doing what I want it to:

It's giving me 0.91, which is the first measure (i.e. 2.75) divided by the second (i.e. 3).

What I want is for it to show 1.25 for the grand total, which is summed total of what it gives above (i.e. 0.25 + 1).

Nevertheless, I still want it to show the 0.25 and 1 on the row totals.

I've tried all kinds of things with SUMX, VALUES, and SUMMERIZE, but nothing seems to work. It needs to be an inline expression, because it should re-calculate depending on the date filter.

Can you help?

[ltripathi]

You can try 

Measure = VAR A = SUM('Table'[Value])/DISTINCTCOUNT('Table'[Date])

Var B =SUMX(   SUMMARIZE(

        'Table',

        'Table'[Type],

        "Row_Total", SUM('Table'[Value]) / DISTINCTCOUNT('Table'[Date])

    ),

[Row_Total])

RETURN

if(HASONEVALUE('Table'[Type]),A,B)

[AltGr9 · Accepted solution]

Ok, I think this is now fixed: 

NewlMeasure =

SUMX(

    VALUES('Table1'[Type]),

    [TotalMeasure]

)

[DataNinja777]

Hi @AltGr9 ,

Sure. In your case, the row-level calculations for each Type are working correctly using the logic [SumMeasure] / [CountMeasure], but the total row is aggregating incorrectly by summing the numerator and dividing by the summed denominator, which gives 0.916 instead of the desired 1.25. To correct this behavior while keeping the row values unchanged, you can use a conditional DAX expression to apply different logic at the total level.

Here's how the corrected measure can be written:

TotalMeasureFix =
IF(
    HASONEVALUE(Table1[Type]),
    DIVIDE([SumMeasure], [CountMeasure]),
    SUMX(
        VALUES(Table1[Type]),
        DIVIDE(
            CALCULATE([SumMeasure]),
            CALCULATE([CountMeasure])
        )
    )
)

This measure checks whether only one value of Type is in context (i.e., a row-level calculation), and applies the usual division. However, when multiple Type values are present (i.e., the total row), it iterates through each Type using SUMX over VALUES(Table1[Type]), and calculates the division for each one individually before summing them together. This ensures your total is the sum of row-level values (0.25 + 1 = 1.25) instead of an average across the whole dataset.

Best regards,

[bhanu_gautam]

@AltGr9 , Try using

TotalMeasure =
SUMX(
VALUES(Table1[ID]),
DIVIDE(
CALCULATE(SUM(Table1[Value]), Table1[Type] = "Type A"),
CALCULATE(DISTINCTCOUNT(Table1[Value]), Table1[Type] = "Type A")
) +
DIVIDE(
CALCULATE(SUM(Table1[Value]), Table1[Type] = "Type B"),
CALCULATE(DISTINCTCOUNT(Table1[Value]), Table1[Type] = "Type B")
)
)

[AltGr9]

Hi bhanu_gautam, 

Thanks for having a go. Unfortunately, the real dataset is far too large to specify each Type explicitly in the Measure text, so that solution is unworkable.

Also, the measure you gave me seemed to return 2.75, rather than the 1.25 I was expecting: