Change Multiple Measures Using A Slicer

[Rupert Bennett]

Hello,
I have a matrix in Power Bi structured as shown below. I would like to use the same visual to show different measures, depending on the slicer selection. By that I mean that if "Revenue" is selected the visual will show revenue values for Actual, Budget and Prior Yr. and the same for any other slicer selection. I do not want to create four different visuals, as this would take up too much screen space. I do not know if this is possible, but I would be very grateful for any help with this.

Slicer

Revenue
GOP
EBITDA
Net Income
===============

Matrix
Property Actual Budget Prior Yr
Prop A 2392211 3445227 2614446
Prop B 3062646 3248356 3191363
Prop B 3394769 3639135 3417960

Thanks,
Rupert

 

[Rupert Bennett]

I just noticed that I have two properties named Property B. The third should Property C. Also, I have measures created for "Actual", "Budget" and "Prior Yr"

 

[Mathexcel]

Hi Rupert,

You need to insert a filter to flick through the different dimensions of your data.

In order to do so, you need to make sure that your data is stored in a way that you can filter it as desired.

You might want to drop a sample of your data set here so I can better advise you!
Best,
Matt

 

[Rupert Bennett]

Hi Matt,
Thank you for your response. Here is a sample of my dataset. I have two data tables, similarly structured, one for Actual data and the other for budget. each Property has actual and budget data for each month of several years.
there are relationships between these two data tables and several lookup tables. I hope I have provided enough information to allow you to create a solution for me. I appreciate your help.


Property_Name Date Act_Rev Act_GOP Act_EBITDA Act_NetIncome
PropertyA 1/1/2018 1,136,378 365,059 263,575 83,720
PropertyA 2/1/2018 1,047,034 388,899 291,238 116,596
PropertyA 3/1/2018 1,227,660 451,030 342,114 161,907
PropertyA 1/1/2019 1,096,571 311,306 205,440 9,997
PropertyA 2/1/2019 979,064 315,869 217,344 28,445
PropertyA 3/1/2019 1,192,104 412,285 299,116 46,253
Property B 1/1/2018 404,227 105,908 28,586 -58,496
Property B 2/1/2018 375,136 107,246 30,852 -56,230
Property B 3/1/2018 415,740 113,601 35,912 -53,577
Property B 1/1/2019 421,028 102,705 24,779 -52,854
Property B 2/1/2019 384,594 116,020 39,257 -38,376
Property B 3/1/2019 473,215 154,556 74,466 -3,167
Property C 1/1/2018 1,478,687 84,740 -34,857 -144,559
Property C 2/1/2018 1,248,332 55,344 -56,903 -156,278
Property C 3/1/2018 1,716,694 278,978 151,787 57,942
Property C 1/1/2019 1,395,688 143,643 22,807 -121,339
Property C 2/1/2019 1,293,582 130,369 12,791 -70,591
Property C 3/1/2019 1,647,161 297,199 168,339 77,446
Property D 1/1/2018 2,172,006 584,842 433,135 -16,387
Property D 2/1/2018 1,893,488 561,142 418,331 -30,728
Property D 3/1/2018 2,565,159 896,483 732,246 303,217
Property D 1/1/2019 2,130,550 527,574 370,894 -74,038
Property D 2/1/2019 1,955,891 578,569 427,481 -14,164
Property D 3/1/2019 2,539,991 855,137 685,456 241,429

Thanks,
Rupert

 

[Mathexcel]

Hi Matt,
Thank you for your response. Here is a sample of my dataset. I have two data tables, similarly structured, one for Actual data and the other for budget. each Property has actual and budget data for each month of several years.
there are relationships between these two data tables and several lookup tables. I hope I have provided enough information to allow you to create a solution for me. I appreciate your help.


Property_Name Date Act_Rev Act_GOP Act_EBITDA Act_NetIncome
PropertyA 1/1/2018 1,136,378 365,059 263,575 83,720
PropertyA 2/1/2018 1,047,034 388,899 291,238 116,596
PropertyA 3/1/2018 1,227,660 451,030 342,114 161,907
PropertyA 1/1/2019 1,096,571 311,306 205,440 9,997
PropertyA 2/1/2019 979,064 315,869 217,344 28,445
PropertyA 3/1/2019 1,192,104 412,285 299,116 46,253
Property B 1/1/2018 404,227 105,908 28,586 -58,496
Property B 2/1/2018 375,136 107,246 30,852 -56,230
Property B 3/1/2018 415,740 113,601 35,912 -53,577
Property B 1/1/2019 421,028 102,705 24,779 -52,854
Property B 2/1/2019 384,594 116,020 39,257 -38,376
Property B 3/1/2019 473,215 154,556 74,466 -3,167
Property C 1/1/2018 1,478,687 84,740 -34,857 -144,559
Property C 2/1/2018 1,248,332 55,344 -56,903 -156,278
Property C 3/1/2018 1,716,694 278,978 151,787 57,942
Property C 1/1/2019 1,395,688 143,643 22,807 -121,339
Property C 2/1/2019 1,293,582 130,369 12,791 -70,591
Property C 3/1/2019 1,647,161 297,199 168,339 77,446
Property D 1/1/2018 2,172,006 584,842 433,135 -16,387
Property D 2/1/2018 1,893,488 561,142 418,331 -30,728
Property D 3/1/2018 2,565,159 896,483 732,246 303,217
Property D 1/1/2019 2,130,550 527,574 370,894 -74,038
Property D 2/1/2019 1,955,891 578,569 427,481 -14,164
Property D 3/1/2019 2,539,991 855,137 685,456 241,429

Thanks,
Rupert

Rupert, at the moment, your measures are stored into a respective field. You can't filter through.
In order to flick through the measures, you would need to have them stored in a column.
So, your desired headers should be = [Property, date, Measure, Values]. Then you can use a filter on the "Measure" field.

I would transform your data sets by Unpivoting the columns, Act_Rev Act_GOP Act_EBITDA Act_NetIncome into a "Measure field", and append both sets together into one. When appending both set, a new column should be created to show wherether the data belongs to the Actual or Budget figures. Headers = [Scenario, Property, date, Measure, Values]

Then you should be able to filter through your data as you want.
I hope this helps, let me know if any questions.
Best,
Matt

 

[Rupert Bennett]

Hi Matt,

Thank you very much for your suggestion. I am a Power BI newbie, but I can immediately see the benefit of restructuring my data as you suggested. It will take me a bit of time in the short term because I will have to recalculate all the measures I use for my visuals and I have many. However, the revised structuring will be immensely beneficial and make it easier to extract the values I need for my many visuals. I know how to take it from here now. Thanks again and have a great day.

Rupert