Forum Discussion

Molin's avatar
Molin
Helper I
3 years ago

Calculating groups

Dear community, 

I am facing a challenge to group sales from a fairly simple table. 

The table have total sales generated on both "OrderType" and "SalesChannel" which sums up to the same. However I would like to change the setup with the following needs, preferably through dax (running direct query): 

 

  • "Dining-In Sales" should only appear once
  • "Pick-Up Sales" should be zeroed
  • "Online Sales" should be calculated as follows: Online Sales - all Providers Sales

The decired result you can see on the right hand side. 

Thanks in advance. 

DAX
Grouped field

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi， Molin 

    You may need create another measure like:

    New Sales =
SWITCH (
    MAX ( 'Table'[Provider] ),
    "Dining-In Sales", BLANK (),
    "Pick-Up Sale", 0,
    "Online Sales", [Online Sales] - [Providers Sales],
    [Sales]
)

    Best Regards,
    Community Support Team _ Eason

  • Molin's avatar
    Molin
    Helper I
    3 years ago

    Hi Eason, 

    Thanks a lot for the help. It all make sense, except the "Online Sales" line as I cant get it to subtract the [Providers Sales]. Do I need a RemoveFilter to calculate the [Providers Sales] or how do I get it to link with the "Online Sales" row? 

    Thanks in advance.