Cumulative measures with SELECTEDVALUE

[DIACHROMA]

Hello everyone ! 

I have three tables where different types of sales are calculated: drive sales, online sales, and in-store sales. I cannot put all of these sales in the same table because they are treated differently.

The customer wishes to have 3 buttons allowing him to change the type of sale in his tables and visualizations. So far everything is fine 😊

I created an "external table" named "Selected Sales" in which I have three lines: Drive sales; Online sales, In-Store sales. Then I created the following measure:

Sales = IF (SELECTEDVALUE ('Selected Sales' [Selected Sales]) = "Drive sales"; [Sum of Drive sales];

IF (SELECTEDVALUE ('Selected Sales' [Selected Sales]) = "Online sales"; [Sum of Online sales];

IF (SELECTEDVALUE ('Selected Sales' [Selected Sales]) = "In-Store sales"; [Sum of In-Store sales])))

I put the “Selected Sales” in a slicer and the view refreshes with the correct measure.

However, my client also wants to be able to add up the sales types when selecting multiple buttons.

Example: If he selects the three he wants total sales. If he selects Drive + Online, he wants these two measures to add up.

I added four rows to my "Selected sales" table and created the corresponding measures:

-   Drive + Online sales

-   Drive + Store sales

-   Online + Store sales

-   Total Sales

I added this to my formula with the corresponding buttons in the slicer.

Except aesthetically, the customer does not like the fact that there are 7 buttons. He ABSOLUTELY wants to be able to combine formulas by selecting several buttons.

How can I do ? Thank you in advance for your help !!!

Pauline

[AlB · Accepted solution]

Hi @DIACHROMA 

I think @camargos88's suggestion is the best way to go. If you want to follow the approach you already had, you could do:

Measure =
VAR selection_ =
    DISTINCT ( 'Selected Sales'[Selected Sales] )
RETURN
     ( "Drive sales" IN selection_ ) * [Sum of Drive sales] + 
     ( "Online sales" IN selection_ ) * [Sum of Online sales] + 
     ( "In-Store sales" IN selection_ ) * [Sum of In-Store sales]

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[DIACHROMA]

Ah yeesss !! It works !! THANK YOU SOOO MUCH @AlB  !

@camargos88 thanks for your help too ! I'm sorry I didn't understand your suggestion.. 😞

I don't have data as example and I'm affraid I can't send you the current one. But I'm curious to understand your solution so if you have any demo I'm all ears ! 🙂

Thank you both !

Pauline

[camargos88]

@DIACHROMA ,

Do you have some data as example, so I can create a demo.

[camargos88]

@DIACHROMA ,

Does it work if you unpivot the type os sales to type/value, and dice/slice it using slicer ? So you just need 1 measure and doesn't matter what he selects, it can sum more than 1 type.

[DIACHROMA]

Hi @camargos88 , 

Thank you for your reply ! I'm not sure to understand.. If I put only the three type of sales in my "Selected Sales" table, when I select two or three types I don't have any result. I don't know how to tell Power BI to sum the two of three measures.