Forum Discussion
Helper IICumulative measures with SELECTEDVALUE
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
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
5 Replies
- AlB
Community Champion
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
camargos88Does 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.
- camargos88
