topic One total value in one column in DAX Commands and Tips

One total value in one column

Anonymous — Tue, 26 Oct 2021 03:30:33 GMT

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!

Re: One total value in one column

VahidDM — Tue, 26 Oct 2021 03:21:15 GMT

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!!

Re: One total value in one column

Anonymous — Tue, 26 Oct 2021 03:34:06 GMT

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

Re: One total value in one column

VahidDM — Tue, 26 Oct 2021 04:46:25 GMT

Anonymous

Did you try the measure I shared before?

Appreciate your Kudos!!

Re: One total value in one column

Anonymous — Tue, 26 Oct 2021 04:58:26 GMT

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

Re: One total value in one column

VahidDM — Tue, 26 Oct 2021 05:25:19 GMT

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!!

Re: One total value in one column

v-robertq-msft — Thu, 28 Oct 2021 03:09:17 GMT

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.