Toggle between different "Show Values As" options in Slicer

0
votes

I am using a Matrix in PowerBI to show the Sales Manager and Sales Reps down the rows, and the different ship method and source of the order across the columns and a count of unique orders in the values.

Below shows the table structure I have, and I want to switch between "Show Values As": the original values I calculate with my measure (count of unique orders) and % of Row Total.

Currently, my only solution is to create a second table showing the different values, but I think that's a cheap solution. I've seen solutions with helper tables but since my values are a measure I cannot invoke them in other measures, and as the sources or ship methods are filtered I want the % of row total to reflect the filter as well so using a countx formula won't account for that (if I housed it in a formula).

I'm still somewhat new to the software so please bear that in mind. 

                     Source 1        |        Source 2         |      Source 3
          |  Next Day; Ground; 2 Day | Next Day; Ground; 2 Day | Next Day; Ground; 2 Day 
+Manager 1| 
   Rep 1  |
   Rep 2  |
+Manager 2|
   Rep 1  |
   Rep 2  |
2
powerbidax

2 Answers

1
votes

Looks like the best solution for you is to create bookmarks. You can create two tables, one showing the values and the other showing the %. Position the tables such that, one table is exactly on top of the other. Then create two bookmarks, one showing Table1 and the other showing Table2 (while hiding the other table using the "Selection Pane"). You can then use the bookmarks in a button action to toggle between Value and % views. The following link should give you further details on bookmarks:

https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

You can also do this by creating a measure which switches between values and %, but the formatting is going to be a pain point. I think bookmarks is your best bet.

0
votes

You can create a parameter table (with no relationships to other tables in your model) to use as a slicer and a measure that switches between the two calculations based on a slicer selection.

Let's suppose you have measures [OrderCount] and [OrderCount%Total]. Then create a new table to use as a parameter ParamMeasure with a single column

Measure
-------
Count
%Total

You can now put ParamMeasure[Measure] as a slicer on your report and substitute the following measure instead of the existing ones.

OrderSwitch =
    IF (
        SELECTEDVALUE ( ParamMeasure[Measure] ) = "Count",
        [OrderCount],
        [Order%Total]
    )

This way you don't need to create multiple visuals and bookmarks.