Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi everyone,
I have another Power BI Matrix Total issue as follows and hope you can help me with it.
I have a made-up dataset (end of this post) that is as close to my real project as possible so you can use it directly to solve the problem.
Final Expected Result
The basic questions are to create a matrix table to count the Headcount and sum the Working Hour by Animal Type with a few tweaks of conditions as follows:
In both targeted outcomes, we limit only Domestic animals except for Dragon (use both wild and domestic).
1. Headcount
Condition: Create a table to count distinct Animal (distinct Animal ID) by Quarter and then sum up the Headcount of 4 quarters for each Animal Type (of course, only domestic animals except Dragon).
Expected Result: I use Excel pivot table to get the result as follows:
2. Working Hour
Condition: Create a table showing the totals of Working Hour of all "Domestic" animals except "Dragon" (count both domestic and wild dragons) by each Animal Type.
Expected Result: I used some sumifs condition in Excel to get this result:
IN POWER BI:
The best I can do give this results with the incorrect totals:
Here are my DAX codes:
HeadCount
HeadCount =
var tot_q1 =
IF (
SELECTEDVALUE(AnimalLabor[Animal Type])="Dragon",
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q1"),
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q1", AnimalLabor[Location]="Domestic")
)
var tot_q2 =
IF (
SELECTEDVALUE(AnimalLabor[Animal Type])="Dragon",
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q2"),
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q2", AnimalLabor[Location]="Domestic")
)
var tot_q3 =
IF (
SELECTEDVALUE(AnimalLabor[Animal Type])="Dragon",
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q3"),
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q3", AnimalLabor[Location]="Domestic")
)
var tot_q4 =
IF (
SELECTEDVALUE(AnimalLabor[Animal Type])="Dragon",
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q4"),
CALCULATE(DISTINCTCOUNT(AnimalLabor[Animal ID]), AnimalLabor[Quarter] = "Q4", AnimalLabor[Location]="Domestic")
)
return tot_q1 + tot_q2 + tot_q3 + tot_q4
Working Hour
Working Hour =
IF (
SELECTEDVALUE(AnimalLabor[Animal Type])="Dragon",
CALCULATE(SUM(AnimalLabor[Hour Working])),
CALCULATE(SUM(AnimalLabor[Hour Working]), AnimalLabor[Location]="Domestic")
)
------------------------------------
Dataset
Location | Animal Type | Animal ID | Hour Working | Quarter |
Domestic | Rat | 1 | 1 | Q1 |
Domestic | Rat | 3 | 4 | Q2 |
Wild | Rat | 2 | 4 | Q1 |
Wild | Rat | 4 | 2 | Q2 |
Wild | Rat | 2 | 9 | Q3 |
Domestic | Rat | 6 | 3 | Q3 |
Wild | Rat | 1 | 5 | Q3 |
Wild | Ox | 1 | 5 | Q4 |
Domestic | Ox | 3 | 2 | Q1 |
Domestic | Ox | 4 | 7 | Q1 |
Wild | Ox | 3 | 2 | Q2 |
Wild | Ox | 7 | 4 | Q4 |
Domestic | Ox | 2 | 7 | Q3 |
Wild | Ox | 5 | 3 | Q2 |
Wild | Tiger | 1 | 4 | Q4 |
Domestic | Tiger | 1 | 6 | Q1 |
Wild | Tiger | 4 | 8 | Q2 |
Wild | Tiger | 2 | 8 | Q3 |
Domestic | Tiger | 3 | 1 | Q4 |
Wild | Cat | 1 | 2 | Q4 |
Wild | Cat | 2 | 3 | Q1 |
Domestic | Cat | 3 | 6 | Q1 |
Domestic | Cat | 2 | 4 | Q2 |
Wild | Cat | 3 | 3 | Q2 |
Wild | Dragon | 1 | 7 | Q4 |
Domestic | Dragon | 2 | 8 | Q4 |
Domestic | Dragon | 2 | 5 | Q1 |
Wild | Dragon | 2 | 2 | Q1 |
Domestic | Dragon | 3 | 4 | Q2 |
Domestic | Dragon | 1 | 5 | Q3 |
Wild | Dragon | 1 | 7 | Q1 |
Solved! Go to Solution.
Hi @mandyhpnguyen ,
This is because the context is difference, please try to create two new measure according to your original measure, try below dax formula:
Adjust Headcount = SUMX(VALUES('Table'[Animal Type]),[HeadCount])
Adjust Working Hour = SUMX(VALUES('Table'[Animal Type]),[Working Hour])
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mandyhpnguyen
Could you use this Measure.
Some times in Matrix Visual Calculate the total wrongly so that case We Can require this error
First thing will Create a one measure
then,
Aply old measure into New measure.If we can apply in variables It won't work thats why This mandatory.
Thanks,
I got the exact solution in the comment below but you helped to explain the case!
I really appreciate it!
Best,
Mandy
Hi @mandyhpnguyen ,
This is because the context is difference, please try to create two new measure according to your original measure, try below dax formula:
Adjust Headcount = SUMX(VALUES('Table'[Animal Type]),[HeadCount])
Adjust Working Hour = SUMX(VALUES('Table'[Animal Type]),[Working Hour])
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks!
This is the correct solution for my particular case!
Best,
Mandy
@mandyhpnguyen , Change the return like this example
return Sumx(Values(AnimalLabor[Animal Type]), calculate( tot_q1 + tot_q2 + tot_q3 + tot_q4))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
141 | |
109 | |
69 | |
55 |