Sumx with IF statement

Hey guys,
I have this measure, Total Sales = SUMX( ‘Tab Sales’, ‘Tab Sales’[UnitPrice] * ‘Tab Sales’[Qty] )
What i would like to do in a measure, not in a column, something like this:

IF ( Tab Sales, [Inv Type] = “NC”
THEN SUMX( ‘Tab Sales’, ‘Tab Sales’[UnitPrice] * ‘(Tab Sales’[Qty] *-1))
ELSE SUMX( ‘Tab Sales’, ‘Tab Sales’[UnitPrice] * ‘Tab Sales’[Qty] )

Thanks a lot
Pedro

Hello @pedroccamara,

Thank You for posting your query onto the Forum.

You can do this by creating a new measure by clicking on the 3 dots and then selecting the option of “New Measure”.

And you can use the formula as given below -

``````Measure Name =
SWITCH( TRUE() ,
[Inv Type] = “NC” ,
SUMX( ‘Tab Sales’, ‘Tab Sales’[UnitPrice] * ‘(Tab Sales’[Qty] *-1) ) ,
SUMX( ‘Tab Sales’, ‘Tab Sales’[UnitPrice] * Tab Sales’[Qty] ) )
``````

Not sure whether this is what you’re looking for.

Thanks & Warm Regards,
Harsh

Hey @Harsh
Thank you for answering me. I’ve tried your solution b4 and it didn’ work.
I’m attaching the xl and the pbi file.
Thank you very much
Pedrotest.pbix (36.5 KB) test.xlsx (14.9 KB)

Hello @pedroccamara,

The “Total Sales” figure which you’ve been getting by the given formulas are correct and I really don’t see any discrepancies neither in your formula nor in mine. The solution which you’re seeing in the table is because the “Quantity” and “Unit Price” column getting summarized.

Below are the screenshots provided for the reference -

Once you uncheck the “Sum” option and select the “Don’t Summarize” option for both “Quantity” as well as “Unit Price” you’ll receive the correct results. Below are the screenshots provided for the reference.

I’m attaching the Excel as well as PBIX file of my working.

Hoping this helps you in your analysis.

Thanks & Warm Regards,
Harsh

test.xlsx (21.3 KB)

test (1).pbix (34.8 KB)

Hey @Harsh
First of all thank you for taking the time for all this. Yes, it was a bad example what i’ve send you but you got the idea. Unfortunately it doesn’t work and i can’t understand cose all those 3 fields are in the same table, you know? I’ve tested with another value and another column and it didn’t work. The purpose of this measure is to have only positive values and as you’re showing me, your example shows no changes in totals, even if the doc type is “NC”. IT should show positive number.
Thanks a lot

Hello @pedroccamara,

Since the “Quantity” for “Doc Type = NC” was negative the totals shown was negative. But you can correct it with the formula given below and change it to a positive number. Below is the formula provided -

``````Sales - Harsh =
SWITCH( TRUE() ,
SELECTEDVALUE( Table1[Doc Type] ) = "NC" ,
ABS( SUMX( Table1 , Table1[Qty] * Table1[UnitPrice] ) ) ,
SUMX( Table1 , Table1[Qty] * Table1[UnitPrice] ) )
``````

And then create another measure to fix the totals with the help of the formula given below -

``````Correct Totals =
SUMX(
SUMMARIZE( Table1 , Table1[Date] , Table1[Doc Type] , "Total Sales" , [Sales - Harsh] ) ,
[Total Sales] )
``````

Now, I’ve the correct sub-totals and grand totals. Below are the screenshots provided -

I’m also providing the PBIX file of my working for the reference as well as providing few links of the videos on how to correct the totals from the education portal.

Hoping you find this useful and helps you in your analysis.

Thanks & Warm Regards,
Harsh

test (1).pbix (48.8 KB)

https://portal.enterprisedna.co/courses/305959/lectures/11981612

https://portal.enterprisedna.co/courses/305959/lectures/13664651

2 Likes

Amazing @Harsh
This is so good. Thank you so much!!!

Hello @pedroccamara,

You’re Welcome.