Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to have 3 matricies setup, the first 2 are working perfectly fine, just the third one down is missing some values in columns that should appear (not divide by 0)
1) Row, # of Sales People
Columns, 3 Contractors , 4 Juniors, 2 Seniors, 1 Executive, 10 Grand Total (formula)
2) Total Sales $
Columns, $150 Contractor, $400 Junior, $400 Senior, $1000 Executive, $1,950 Grand Total (formula)
3) $ / Sales Person
ERROR: Only $/Sales Person of the Grand Total column appears ($195.00), even though other columns should. Missing average sales of Contractor ($50), Junior ($100), Senior ($200), and Executive ($1000).
I have made the following 3 variables:
Num of Sales People = SUM(datatable[HeadCount])
Total Sales = SUM(datatable[GrossSales])
Average Sale = DIVIDE([Total Sales].[Num of Sales People])
Even when I do DIVIDE([Total Sales].[Num of Sales People],0), it gives me $0s where you would expect to see $50, $100, $200 etc.
Am I missing something?
Edit:
I tried adding CALCULATE( to both Num of Sales People and Total Sales thinking it would help the column filters from the Matricies. It didn't work.
Solved! Go to Solution.
Hi @Anonymous,
What visual did you use? Since you invoked two measures, they should be in the same context. It could be like below.
Column | # of Sales People | Sales $ | Avg Sales / Person |
Contractors | 3 | 150 | 50 |
Juniors | 4 | 400 | 100 |
Seniors | 2 | 400 | 200 |
Executive | 1 | 1000 | 1000 |
total | 10 | 1950 | 195 |
To be honest, your scenario is simple. If you still have questions, please provide a sample.
Best Regards,
Dale
Hi Dale,
When I designed it another view to troubleshoot it further, it was because I had 'Juniors/Contractors/etc." column sorted based on a different column "RoleSortOrder".
For some reason, Contractors whom I typically want first, was sometimes sorted as 1 and sometimes sorted as 2.
Once I cleaned up this SortOrder to be 100% consistent, PowerBI combined them apporpriately.
Thanks so much Dale!
Hi @Anonymous,
What visual did you use? Since you invoked two measures, they should be in the same context. It could be like below.
Column | # of Sales People | Sales $ | Avg Sales / Person |
Contractors | 3 | 150 | 50 |
Juniors | 4 | 400 | 100 |
Seniors | 2 | 400 | 200 |
Executive | 1 | 1000 | 1000 |
total | 10 | 1950 | 195 |
To be honest, your scenario is simple. If you still have questions, please provide a sample.
Best Regards,
Dale
Hi Dale,
When I designed it another view to troubleshoot it further, it was because I had 'Juniors/Contractors/etc." column sorted based on a different column "RoleSortOrder".
For some reason, Contractors whom I typically want first, was sometimes sorted as 1 and sometimes sorted as 2.
Once I cleaned up this SortOrder to be 100% consistent, PowerBI combined them apporpriately.
Thanks so much Dale!
(Other post was flagged as SPAM incorrectly, sorry to the Moderators for the repost)
I want to have 3 matricies setup, the first 2 are working perfectly fine, just the third one down is missing some values in columns that should appear (not divide by 0)
1) Row, # of Sales People
Columns, 3 Contractors , 4 Juniors, 2 Seniors, 1 Executive, 10 Grand Total (formula)
2) Total Sales $
Columns, $150 Contractor, $400 Junior, $400 Senior, $1000 Executive, $1,950 Grand Total (formula)
3) $ / Sales Person
ERROR: Only $/Sales Person of the Grand Total column appears ($195.00), even though other columns should. Missing average sales of Contractor ($50), Junior ($100), Senior ($200), and Executive ($1000).
I have made the following 3 variables:
Num of Sales People = SUM(datatable[HeadCount])
Total Sales = SUM(datatable[GrossSales])
Average Sale = DIVIDE([Total Sales].[Num of Sales People])
Even when I do DIVIDE([Total Sales].[Num of Sales People],0), it gives me $0s where you would expect to see $50, $100, $200 etc.
Am I missing something?
Edit:
I tried adding CALCULATE( to both Num of Sales People and Total Sales thinking it would help the column filters from the Matricies. It didn't work.
Here's a quick Excel mockup of what I'd hope the PowerBI to be able to show in 3 separate matricies.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |