Fix Matrix Total in Dax Measure Column with Condition and Variables in Power BI?

[mandyhpnguyen]

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

[Thennarasu_R]

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.

1  Actual Sales=Calculate(SUM(Sales))

2  Total Output  = Calculate(SUMX( VALUES (Accuracy[Product Name]), calculate (SUMX( VALUES (Accuracy[Product Name]), [ Actual Sales] )))

)


Thanks ,
Thennarasu

[mandyhpnguyen]

Thanks,

I got the exact solution in the comment below but you helped to explain the case!

I really appreciate it!

Best,

Mandy

[Anonymous · Accepted 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.

[mandyhpnguyen]

Thanks!

This is the correct solution for my particular case!

Best,

Mandy

[amitchandak]

@mandyhpnguyen , Change the return like this example

return Sumx(Values(AnimalLabor[Animal Type]), calculate( tot_q1 + tot_q2 + tot_q3 + tot_q4))

[mandyhpnguyen]

@amitchandak I did but the result stayed the same. Did it work in your .pbix?