Issues with Totals for Dax Measure

DAX DAX Calculations
1

Hi all,

I have a DAX measure which is giving me the results for each row as expected, but at the end it should also give me the totals, so in this case 42.18.
I have looked at the forum to find a solution, but whatever I tried, it all returns a total of 0,00 in the end.

This is the measure:

Marge issue =

IF(
[DGM %] < 0.49,
[Commission]*-1, BLANK() )

This is what i have tried so far:

Marge 2 =
SUMX(
ADDCOLUMNS(
VALUES
(‘Clients Thomas’[Code]),
“Client” , ‘Clients Thomas’[Code],
“Margin Corr” , [Marge issue] ),
[Marge issue]
)

Marge 3 =
IF(
HASONEVALUE( ‘Clients Thomas’[Code]),
[Marge issue],
SUMX(
ADDCOLUMNS(
VALUES( ‘Clients Thomas’[Code]),
“Marge issue”, [Marge issue]
),
[Marge issue]
)
)

and this one:

Marge 4 = SUMX
(SUMMARIZE(
‘Clients Thomas’,
‘Clients Thomas’[Code]),
[Marge issue])

It would be great if somebody can help me to get this fixed…

Many thanks!!

Marge issue
Marge issue921×617 65.8 KB

2

please see if this post from @Greg will help to steer you in the right direction

if this does not help, then please provide a sample file that forum members can use to try to help you solve your issue.

3

@marieke
What is [DGM %] at the grand total?

4

Hi @Heather

Thank you, I know about that topic from Brian and have tried several of his solutions, but without any luck unfortunatelly…

I have attached a copy of the PBI file, it would be great if somebody knows how to fix this…

Sale Commission Thomas Dummy.pbix (743.9 KB)

5

Hi @AntrikshSharma

I have now attached a copy of the file to make things more clear…(hopefully)

6

Hello @marieke,

Thank You for posting your query onto the Forum.

To achieve the results of “Grand Totals” below is the formula alongwith the screenshot of the result provided for the reference -

Marge - Harsh = 
SUMX(
    SUMMARIZE(
        VerkoopFacturen ,
        'Clients Thomas'[Code] , 
        Debiteuren[KlantNaam] , 
        VerkoopFacturen[FactuurNummer] , 
        VerkoopFacturen[FactuurRegel] , 
        "Totals" , 
    [Marge issue] ) , 
[Totals] )

Result 1
Result 11273×679 201 KB

And if you want to convert the numbers and totals into the postive from negative than just use “ABS()” function. Below is the formula and screenshot of the result provided for the reference -

Marge - Harsh = 
SUMX(
    SUMMARIZE(
        VerkoopFacturen ,
        'Clients Thomas'[Code] , 
        Debiteuren[KlantNaam] , 
        VerkoopFacturen[FactuurNummer] , 
        VerkoopFacturen[FactuurRegel] , 
        "Totals" , 
    ABS( [Marge issue] ) ) , 
[Totals] )

Result 2
Result 21273×674 189 KB

Also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: The figure is off by 0.01 and this could due to decimal formatting. If we move the decimal portion to 3 places than we get the results as 42.187.

Thanks and Warm Regards,
Harsh

Sale Commission Thomas Dummy.pbix (743.5 KB)

1 Like
7

@marieke,

I was working this one at the same time as @Harsh, and came up with a nearly identical solution:

Marg Issue2 =

VAR Result =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                'VerkoopFacturen',
                'Clients Thomas'[Code],
                'Debiteuren'[KlantNaam],
                'VerkoopFacturen'[FactuurNummer],
                'VerkoopFacturen'[FactuurRegel]
            ),
            "@BaseMeas", [Marge issue]
        ),
        [@Basemeas]
    )
RETURN
    Result

image
image712×560 44.7 KB

My solution does have one important difference - when adding extension columns to a table, it is a best practice to do so using ADDCOLUMNS, rather than SUMMARIZE.

I hope this is helpful.

  • Brian
3 Likes
8

Hi @BrianJ and @Harsh ,

Many thanks for the solutions you both provided, they both works fine, I have decided to use the version from Brian with the ADDCOLUMNS…

Many thanks for your help with this!

Kind regards Marieke