Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create Charts based on matrix

Hi Everyone,

 

I have this project where I have to create some line and clustered column charts based on values from a Matrix table. The main issue is that :

- The matrix table is based on calculated measures that can change depending of slicers.

- The Line and clustered column charts have the same slicers and must change also depending of slicers.

 

From now even if the values are displayed in the matrix table, I can't use them to build the charts. Do someone know if it is possible with PBI ?

 

Here is more details about the project.

 

Project of Comparison/benchmarking betweem companies.

Exemple of the matrix :

 

Company      Measure       Values    MarketAverage    Market Median

Company1    Salary            10000      15000                   14000

Company2    Salary             12000      14000                   13000

Company3    Salary            11000      13000                   14000

Company2   Bonus            2000         1400                      1300

Company1    Bonus            1000        1500                      1400

Company2    Bonus           1200        1000                     1300

 

Median & Average are calcultated measures (average of all the company - company selected)

 

Charts are about % beween value and median market or % difference beween salary & Bonus compared to market%

 

Thanks for your help.

Need Help
Tips and Tricks
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, please try to create two seperate columns as slicers:

    Selected company = DISTINCT(SELECTCOLUMNS('Table',"Selected company",'Table'[Company]))
    Compare with = DISTINCT(SELECTCOLUMNS('Table',"Compare with",'Table'[Company]))

    Then create a measure to calculate the average of 'Salary':

    Measure = CALCULATE(AVERAGE('Table'[Values]),FILTER('Table',('Table'[Company]=SELECTEDVALUE('Selected company'[Selected company])||'Table'[Company]=SELECTEDVALUE('Compare with'[Compare with]))&&'Table'[Measure]="Salary"))

    When you choose the slicer, it shows the comparsion:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    One way is first unpivot

    https://radacad.com/pivot-and-unpivot-with-power-bi

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      Thanks for your reply however this won't work.

      As said the matrix table is based on slicers and will compare a company with another one or a groupe of other companies.

       

      Exemple :

      5 companies : Company 1, Company 2, Company 3, Company 4 & Company 5

      5 Salaries : Salary 1, Salary 2, Salary 3, Salary  4 & Salary 5

      2 slicers "Selected Company" and "Compare with"

      The matrix table will automatically calculate the Salary Average for the selected companies in "Compare with" slicer excluding the company selected in "Selected company" slicer.

       

      But the selected company can be compare to 1, 2, 3 or 4 others companies so I don't really see how to Unpivot this dynamic matrix.

       

      Let me know if you need more details.

       

      Thanks

       

       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        According to your description, please try to create two seperate columns as slicers:

        Selected company = DISTINCT(SELECTCOLUMNS('Table',"Selected company",'Table'[Company]))
        Compare with = DISTINCT(SELECTCOLUMNS('Table',"Compare with",'Table'[Company]))

        Then create a measure to calculate the average of 'Salary':

        Measure = CALCULATE(AVERAGE('Table'[Values]),FILTER('Table',('Table'[Company]=SELECTEDVALUE('Selected company'[Selected company])||'Table'[Company]=SELECTEDVALUE('Compare with'[Compare with]))&&'Table'[Measure]="Salary"))

        When you choose the slicer, it shows the comparsion:

        Hope this helps.

         

        Best Regards,

        Giotto Zhi