cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular Visitor

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

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).

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 =
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")
)

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
1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Responsive Resident

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
Regular Visitor

Thanks,

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

I really appreciate it!

Best,

Mandy

Community Support

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.

Regular Visitor

Thanks!

This is the correct solution for my particular case!

Best,

Mandy

Super User

@mandyhpnguyen , Change the return like this example

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

Regular Visitor

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors