Results 1 to 6 of 6

Thread: Change Multiple Measures Using A Slicer
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change Multiple Measures Using A Slicer

    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

  2. #2
    Board Regular
    Join Date
    Nov 2002
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Multiple Measures Using A Slicer

    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"

  3. #3
    New Member
    Join Date
    Jun 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Multiple Measures Using A Slicer

    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

  4. #4
    Board Regular
    Join Date
    Nov 2002
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Multiple Measures Using A Slicer

    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

  5. #5
    New Member
    Join Date
    Jun 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Multiple Measures Using A Slicer

    Quote Originally Posted by Rupert Bennett View Post
    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

  6. #6
    Board Regular
    Join Date
    Nov 2002
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Multiple Measures Using A Slicer

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •