Latest Enterprise DNA Initiatives


SUMX not calculating correctly

DAX Calculations
#1

Hi

Hoping someone can help as I’m not sure what the best way is of going about this…

I have a table that looks like below where I’m trying to have Volumes in Table B multiplied with a rebate amount contained in Table C using SUMX( Table A, 'Table B [Volume Value] * 'Table C [Rebate Value])

The rebate table C is a table showing rebates by Zone which I cant join with Table A (without creating a Many to many relationship)
image

Initial thoughts are to using something like the below to get a unique rebate value or using SUMMARIZE to allow for valid combinations in SUMX although in both instances SUMX doesn’t calculate correctly although I could be missing something simple

= CALCULATE( min(‘Table C’[Rebate Value]) ,FILTER(‘Table C’,‘Table C’[Site] = FIRSTNONBLANK(‘Table A’[Zone],‘Table A’[Zone])))

Thanks in advance

#2

Can you try below code and see the result?

= CALCULATE( min(‘Table C’[Rebate Value]) ,FILTER(ALL(‘Table C’[Site] ),‘Table C’[Site] = FIRSTNONBLANK(‘Table A’[Zone],‘Table A’[Zone])))

#3

Hi Hafiz

Thanks that works now. Are you able to share what ALL() does in this particular context? Understanding the context behind the dax logic is tricky

Thanks

Richard

#4

Hi @richardsim,

Great that it worked for you :slight_smile:

That is somewhat tricky concept and it is related to expanded table. You can view concept of expanded table on below link:

Basically ALL(‘Table C’[Site]) removes all filters from that column and it does not bring all columns from expanded table of Table C.

#5

Thanks Hafiz :slight_smile:

1 Like
#6

@hafizsultan,

I am really enjoying your posts on the forum. One suggestion for you (and for everyone who posts questions or solutions ) though - the forum toolbar has an option to reformat your DAX if it lost the formatting after you pasted into your post. Just highlight your DAX code and click on the icon in the toolbar the looks like this: </>

Even for relatively simple measures it makes a big difference in readability, and for complex ones it’s invaluable. These are the same measure below - personally I struggle to see what’s going on in the first one, but see immediately how the second one works, despite it being the exact same code.

To ensure the format button works properly, be sure to leave a blank line between your "measure name = " and the start of the DAX code

Measure = CALCULATE( min(‘Table C’[Rebate Value]) ,FILTER(ALL(‘Table C’[Site] ),‘Table C’[Site] = FIRSTNONBLANK(‘Table A’[Zone],‘Table A’[Zone])))

Measure =

CALCULATE (
    MIN ( 'Table C'[Rebate Value] ),
    FILTER (
        ALL ( 'Table C'[Site] ),
        'Table C'[Site] = FIRSTNONBLANK ( 'Table A'[Zone], 'Table A'[Zone] )
    )
)

Thanks very much for your efforts in developing solutions to forum Qs.

  • Brian
1 Like
#7

Thank you @BrianJ , you are so helpful. I was also thinking same that how do you get formatting right in the code and whenever i paste my formula, all formatting is gone :slight_smile: , infact, I was about to ask from you same question.

Indeed, I am learning a lot from your posts as well as @Melissa posts in this forum. Thank you guys for being so helpful.

2 Likes