topic Re: Sum the result of each row the total sum in the total subtotal rows in DAX Commands and Tips

Sum the result of each row the total sum in the total subtotal rows

JNMx — Thu, 24 Mar 2022 21:02:29 GMT

Hello

I would like to get a different result depending on 2 columns:

Sales Last Year

Sales Current Year

if current year sales are 0 I classify this as "Discontinued" and I would like to have a column displaying that in a table.

I have achieved this with Dax with no problem BUT check the total row, it displays Zero, and I would like to have the sum of the column "Sum if CY is 0", e.g. -3, -2, -1, -2-

I understand that the measure I am using will not apply in total because the Sales CY in total is not 0; but I don't know how to sum the rows.

Thanks in advance for your time and attention.

Re: Sum the result of each row the total sum in the total subtotal rows

Whitewater100 — Thu, 24 Mar 2022 23:50:52 GMT

Hello:

You can add a calculated column to your table. I'm calling the table "Data". It just has a bit of conditional logic.

Then a measure to sum up the negatives only. Please see below. I hope this helps!

Calc Col Sum Neg YoY = IF(Data[Var] < 0, Data[Var],BLANK())

Measure

Neg Var Total = SUM(Data[Sum Neg YoY])

Re: Sum the result of each row the total sum in the total subtotal rows

JNMx — Fri, 25 Mar 2022 16:22:20 GMT

Unfortunately those aren't physical columns, those are measures, that's the difficulty.

Re: Sum the result of each row the total sum in the total subtotal rows

v-easonf-msft — Wed, 06 Apr 2022 02:27:56 GMT

Hi, JNMx

To change the wrong total, please try measure as below:

Measure = SUMX ( VALUES ( Table[Item] ), [Sum IF CY is 0] )

Best Regards,
Community Support Team _ Eason