Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

One total value in one column

I have a table with various product categories 

Category Previous Sales Current Sales Total Sales
Socks 2,000 2,000
Hangers500 500
Pants20400400
Jacket0300300
Shirt1,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!

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1635390469538.png

 

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.

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1635390469538.png

 

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
Super User
Super User

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
Not applicable

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

@Anonymous 

 

Did you try the measure I shared before?

 

Appreciate your Kudos!!

 

Anonymous
Not applicable

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

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.