Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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!

  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    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.

6 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        VahidDM
        Icon for Super User rankSuper User
        4 years ago

        Anonymous 

         

        Did you try the measure I shared before?

         

        Appreciate your Kudos!!

         

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.