One total value in one column

[Anonymous]

I have a table with various product categories 

Category	Previous Sales	Current Sales	Total Sales
Socks	2,000		2,000
Hangers	500		500
Pants	20	400	400
Jacket	0	300	300
Shirt	1,000		1,000

I would like to create a measure with dax in this case 'Total Sales', where I would like to only display the Total sales values for each category. Ideally once i drag the Total Sales measure it calculates the merged values from the Previous and Current Sales columns. I'm relatively new to PowerBI please help!

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, do you mean that you want to prioritize the current value as the total value and get the total value for each category? I think you can try to create a measure like this:

Total Sale =

SWITCH(

    TRUE(),

    MAX('Table'[Current Sales ])<>BLANK(),SUM('Table'[Current Sales ]),

    MAX('Table'[Previous Sales ])<>BLANK(),SUM('Table'[Previous Sales ]),

    SUM('Table'[Current Sales ]))

And you can create a table chart like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[VahidDM]

Hi @Anonymous 

Try this:

measure = [Previous Sales]+[Current Sales]

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

Appreciate your Kudos!!

[Anonymous]

Hi, Thanks for replying. Though for my pants row my Total sales will be 420. Total Sales value should be based on the sales values in each column. i.e for pants it should be 400 not 420

[VahidDM]

@Anonymous 

Did you try the measure I shared before?

Appreciate your Kudos!!

[Anonymous]

Yes, the result for pants will be 420 with that measure instead of 400

[VahidDM]

So if there is a value in Current Sales you want to use that, otherwise, you want to see the Previous Sales. am I correct? if yes, try this:

measure =
Var _CS =Sum([Current Sales])
Var _PS = SUM([Previous Sales])
return
if(isblank(_CS),_PS,_CS)

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

Appreciate your Kudos!!