Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Buttons that change data labels

Hi all,

I got a task from my colleagues and I do have totally no option how to do it.

 

Basics: We do have a table who contains Sales Documents, division and other measures

 

Requirement: 

- Creating visuals (that show i.e. how many Sales Documents were created per division)

- Create two buttons in the report: "Absolute" and "Percentage".

Clicking on either button should change the data labels of all visuals on the page (or bettto display the values either in total or in percentage of total.

I.e. Clicking the "Absolute" button changes the count in Division "O" from "1.100" to "10%".

And I do habe absolutely no clue how to do this without programming something fancy, which is critical, cause I couldn't code for my life...

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Anonymous If you want them to be able to choose, you'll need to get a little fancy with measures AND bookmark or parameter slicer. If you want to display both, you only need measures.

 

Create new measures: 

 

Absolute Value = COUNT(Table[SalesDocuments])

Percent = DIVIDE([Absolute Value], CALCULATE([Absolute Value], ALL(Table[Division]))

 

Create a new table visual, with both measures in it and Division.

 

Now for the fancy part: 

 

Option A: Slicer

Click 'Enter Data'

Paste this table: 

 

Show Value As
Absolute
Percent

 

Put Slicer[Show Value As] column in a slicer visual.

 

Create a new measure: 

 

Value = SWITCH(SELECTEDVALUE(Slicer[Show Value As])
, "Absolute", [Absolute Value]
, "Percent", FORMAT([Percent], "#.00%")
, [Absolute Value])

 

Put the [Value] measure in your table and test the slicer.

 

Option B: Bookmarks

Duplicate the table. Remove [Percentage] from one table and remove [Absolute Value] from the other table. Hide the Percentage table, create a new bookmark, name it 'Absolute Value'. Click the three dots and untick 'data' and 'current page'. Now unhide percentage table and hide absolute value table. Repeat bookmark creation process, but name it 'Percentage'. 

Add a button for each bookmark.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

11 REPLIES 11
AllisonKennedy
Super User
Super User

@Anonymous  Excellent questions. I have added a file to my original post that hopefully makes it more clear. I also updated the DAX to use SELECTEDVALUE and to account for if there is no selection. I also added formatting to get the percent formatted properly. 

 

Let me know if you have any other queries. Please @  mention me so I don't miss it. 🙂 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi @AllisonKennedy thank you very much for the answers!

I understand now how you did it, however for whatever reason I wasn't able to achieve the same results. I created a dummy dataset to try it in those simplified conditions, but it didn't work as well. So I would very much appreciate if you could take a swift look and tell me where I've gone wrong... From my point of view I did it exactly as instructed...

 

 

Only things I changed were the names of the tables (but I adapted them in the formula), choose "SaTy" instead of Division but, like said, I adjusted the formula accordingly and I removed the Measures from the Value field in the visual to achieve the desired look. But also adding them back didn't change a thing an I realy can't tell why it doesn't work. I first thought its because the Measures aren't named exactly equal to the values in the slicer, but the formula for the Value-measure takes care of that so can't be that...

 

Can't attach samples so I'll just send screenshots:

WW1.PNGWW2.PNGWW3.PNGWW4.PNGWW5.PNGWW6.PNG

@Anonymous You made the same error I made in my original post - the measure says 'Percent' but the slicer says 'Percentage'. They must both match. Update your Value measure to say 'Percentage' and it should work.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi @AllisonKennedy that was exactly it! Thank you so much as it works now 🙂

 

One more question would be how to change the way its displayed in other visuals?

E.g. in the pie chart it doesn't make a difference if I use the "Value measurement" or not. I need the "SaTy Percent" measurement anyway cause the Value measurement seems not sufficient for PowerBI to build a pie chart in Percent (guess because PowerBI Can't calculate in percent?).

Unfortunately, then the "Percent" tags are not displayed as percent anymore, but as decimals:

Sab10.PNGSab11.PNG

What I should remark: I changed the data label to "Values", cause otherwise it also displays the percentage (not desired) when slicer is on "Absolute". WHen I change the data label to "Percentage of total for example, that issues isn't anymore, but then I got **bleep** 0% in the gapes of the chart:

Sab12.PNG

Its all a big misery tho...

@Anonymous  Yay! Glad it's working, kudos for getting it done! 

 

For the pie chart, I'm not sure why you have all three measures displayed in the pie chart? The 0% Will be the 'SalesDocPercent' field, since it's a percent, the numbers are much smaller so they are displaying as zero percent of total just because they are so small. Pick one measure only for the pie chart, then make sure you have that measure formatted properly (as a percent if you want the SalesDocPercent value to display as percent). 

 

Not sure if that's what you're asking or if it makes sense?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Yeah I know what you mean... unfortunately thats not what my colleagues require me to do...:/ I guess I'll just have to try it with bookmarks then and see if I can configure them as I want (apply slicer, interactive measures etc...)

AllisonKennedy
Super User
Super User

@Anonymous  The same slicer can update the values among all measures, but you'll need to create a measure for each calculation, so if 5 metrics, 10 measures. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Dear@AllisonKennedy,

I tried your method and stumbled over a few questions, which I'll ad in Italic to your instructions:

"

Create new measures: 

 

Absolute Value = COUNT(Table[SalesDocuments])

Percent = DIVIDE([Absolute Value], CALCULATE([Absolute Value], ALL(Table[Division]))

 

Create a new table visual, with both measures in it and Division. Do you mean I should create a visuals that is a table and put both measures in or do you mean I should create a new table in the "Data section", name it "visual" and out both measures in it?

 

Now for the fancy part: 

 

Option A: Slicer

Click 'Enter Data'

Paste this table: 

 

Show Value As
Absolute
Percentage

 

Put Slicer[Show Value As] column in a slicer visual. I think that means I should name the Table Slicer[Show Value As]? If I don't and enter the dax formula below it tells me that it "can't find table Slicer [Show Value As]".

 

Create a new measure: 

 

Value = SWITCH(Slicer[Show Value As]

, "Absolute", [Absolute Value]

, "Percent", [Percent])

like said, if the Name of Slicer table is just "Slicer" and the one of the first is "Show Value As", formula doesn't work, it seems to expect a table as expression.

 

Put the [Value] measure in your table and test the slicer Which table? The visual that is a table or a data table? If yes, which one?"

 

Sorry for the partly stupid and very specific questions, but I was a little confused by the meanings of the word table and I must have taken a wrong turn somewhere cause it didn't work out in the end...

 

Help would be much appreciated cause I thiink your method is probably the best one for my usecase

AllisonKennedy
Super User
Super User

@Anonymous If you want them to be able to choose, you'll need to get a little fancy with measures AND bookmark or parameter slicer. If you want to display both, you only need measures.

 

Create new measures: 

 

Absolute Value = COUNT(Table[SalesDocuments])

Percent = DIVIDE([Absolute Value], CALCULATE([Absolute Value], ALL(Table[Division]))

 

Create a new table visual, with both measures in it and Division.

 

Now for the fancy part: 

 

Option A: Slicer

Click 'Enter Data'

Paste this table: 

 

Show Value As
Absolute
Percent

 

Put Slicer[Show Value As] column in a slicer visual.

 

Create a new measure: 

 

Value = SWITCH(SELECTEDVALUE(Slicer[Show Value As])
, "Absolute", [Absolute Value]
, "Percent", FORMAT([Percent], "#.00%")
, [Absolute Value])

 

Put the [Value] measure in your table and test the slicer.

 

Option B: Bookmarks

Duplicate the table. Remove [Percentage] from one table and remove [Absolute Value] from the other table. Hide the Percentage table, create a new bookmark, name it 'Absolute Value'. Click the three dots and untick 'data' and 'current page'. Now unhide percentage table and hide absolute value table. Repeat bookmark creation process, but name it 'Percentage'. 

Add a button for each bookmark.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi @AllisonKennedy 

I think by doing this I have to do it for all values I want to show in both absolute and total isn't it?

Cause the Sales document mentioned above was just one example of like, 5... there's no "general option" to do it isn't it?

Like, I got an idea but it surely is more effort than to do it for each of the respective columns manually...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.