Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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
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
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
Hi,
Thanks for your reply. Indeed I have created 2 disconnected tables in order to be able to solve the issue.
🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |