DAX measure does not override total in matrix

[Spanggaard]

Hi all,

I've used the code below to calculate the difference between two values in a matrix - it does the job, but the actual numbers overrides my dax in regards to the total columns.

e.g. the total column in 'fælles' says that we have a total of above 800.000 but is in fact only 7.571.

Also I had to manipulate data to remove the numbers where total = 0 - but it is not on purpose that that the total is zero, it should be the sum of the columns ..

Difference = 
VAR thisforecastnum =
    MIN ( Costcenter[Kost center] )
VAR thisforecast =
    SUM ( Costcenter[Faktisk Beløb] )
var lastforecastnum=CALCULATE(MAX('Costcenter'[Kost center]),FILTER(ALLSELECTED(costcenter),'Costcenter'[Kost center]<MAX('Costcenter'[kost center])))
VAR lastforecast =
    CALCULATE (
        SUM ( Costcenter[Faktisk Beløb] ),
        Costcenter[Kost center] = lastforecastnum
    )
RETURN
    IF (
        ISBLANK (lastforecast),
        SUM ( Costcenter[Faktisk Beløb]),
        IF(max('costcenter'[Kost center]) = "Fælles",
        lastforecast - thisforecast,
        BLANK()
    )
    
)

new_amount = IF(
     IF(sum('Costcenter'[Faktisk Beløb]) == Costcenter[Difference] && max(costcenter[Kost center]) = "fælles",
     BLANK() + 0,
     sum(Costcenter[Faktisk Beløb])),
     IF(sum('Costcenter'[Faktisk Beløb]) <> Costcenter[Difference] && max(costcenter[Kost center]) = "fælles",
     costcenter[Difference], 
     sum(Costcenter[Faktisk Beløb]))

)

[Anonymous · Accepted solution]

Hi @Spanggaard ,

Based on your description, you can create a measure.

Measure =

SUMX(

    SUMMARIZE(

        'Table',

        'Table'[RowName],

        'Table'[ColumnName],

        "total",SUM ( 'Table'[Your Measure] )

    ),

    [total]

)

You can refer to the following documents that may be helpful to you:

How to Make Measures Total Correctly in Power BI Tables - ArcherPoint

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.