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.
Good day!
Simple raw data in 2 table as below.
Name | Whole year target |
Elijah | 54 |
Liam | 82 |
Noah | 70 |
Oliver | 68 |
William | 100 |
Name | Month | Month_number |
Liam | Jan | 0 |
Liam | Feb | 8 |
Liam | Mar | 0 |
Noah | Jan | 0 |
Noah | Feb | 9 |
Noah | Mar | 9 |
Oliver | Jan | 10 |
Oliver | Feb | 4 |
Oliver | Mar | 3 |
William | Jan | 10 |
William | Feb | 0 |
William | Mar | 1 |
Elijah | Jan | 8 |
Elijah | Feb | 7 |
Elijah | Mar | 7 |
I want to have a visual to show the month values per name, also the total numbers over the Target (in percentage), ideally like:
Name | Jan | Feb | Mar | Sub-Total | Whole year target | Sub-Total over Target |
Elijah | 8 | 7 | 7 | 22 | 54 | 41% |
Liam | 0 | 8 | 0 | 8 | 82 | 10% |
Noah | 0 | 9 | 9 | 18 | 70 | 26% |
Oliver | 10 | 4 | 3 | 17 | 68 | 25% |
William | 10 | 0 | 1 | 11 | 100 | 11% |
With the settings, I can only come up with a matrix as below, and don't know what to do next.
Can you please help me? Thank you.
Solved! Go to Solution.
Hi @JohnnyK ,
1)You will be required to make the relationship between two tables filtered in both directions
2) And you would be required to create below measures -
(1) Jan = CALCULATE(sum('MOnthly values'[Month_number]),'MOnthly values'[Month]="Jan")
(2) Feb = CALCULATE(sum('MOnthly values'[Month_number]),'MOnthly values'[Month]="Feb")
(3) Mar = CALCULATE(sum('MOnthly values'[Month_number]),'MOnthly values'[Month]="Mar")
(4) SubTotal = CALCULATE(SUM('MOnthly values'[Month_number]),ALLEXCEPT('MOnthly values','MOnthly values'[Name]))
(5) Total target = CALCULATE(SUM('Target values'[Whole year target]))
(6) % = 'MOnthly values'[SubTotal]/'MOnthly values'[Total target]
3) And pull these measures along with Name column in the Table visual, it will give the below result
Hope this helps.
Please accept the solution if this answers your query.
Thanks!
Avantika
Hi @JohnnyK ,
1)You will be required to make the relationship between two tables filtered in both directions
2) And you would be required to create below measures -
(1) Jan = CALCULATE(sum('MOnthly values'[Month_number]),'MOnthly values'[Month]="Jan")
(2) Feb = CALCULATE(sum('MOnthly values'[Month_number]),'MOnthly values'[Month]="Feb")
(3) Mar = CALCULATE(sum('MOnthly values'[Month_number]),'MOnthly values'[Month]="Mar")
(4) SubTotal = CALCULATE(SUM('MOnthly values'[Month_number]),ALLEXCEPT('MOnthly values','MOnthly values'[Name]))
(5) Total target = CALCULATE(SUM('Target values'[Whole year target]))
(6) % = 'MOnthly values'[SubTotal]/'MOnthly values'[Total target]
3) And pull these measures along with Name column in the Table visual, it will give the below result
Hope this helps.
Please accept the solution if this answers your query.
Thanks!
Avantika
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |