Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mandyhpnguyen
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

mandyhpnguyen_2-1675902332771.png

  

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:

mandyhpnguyen_1-1675902293216.png

 

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:

mandyhpnguyen_3-1675902424719.png

 

IN POWER BI:

The best I can do give this results with the incorrect totals:

mandyhpnguyen_4-1675903001201.png

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

 

LocationAnimal TypeAnimal IDHour WorkingQuarter
DomesticRat11Q1
DomesticRat34Q2
WildRat24Q1
WildRat42Q2
WildRat29Q3
DomesticRat63Q3
WildRat15Q3
WildOx15Q4
DomesticOx32Q1
DomesticOx47Q1
WildOx32Q2
WildOx74Q4
DomesticOx27Q3
WildOx53Q2
WildTiger14Q4
DomesticTiger16Q1
WildTiger48Q2
WildTiger28Q3
DomesticTiger31Q4
WildCat12Q4
WildCat23Q1
DomesticCat36Q1
DomesticCat24Q2
WildCat33Q2
WildDragon17Q4
DomesticDragon28Q4
DomesticDragon25Q1
WildDragon22Q1
DomesticDragon34Q2
DomesticDragon15Q3
WildDragon17Q1
1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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

vbinbinyumsft_0-1676009045311.png

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.

View solution in original post

6 REPLIES 6
Thennarasu_R
Responsive Resident
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

Thanks,

 

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

 

I really appreciate it!

 

Best,

Mandy

v-binbinyu-msft
Community Support
Community Support

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

vbinbinyumsft_0-1676009045311.png

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

amitchandak
Super User
Super User

@mandyhpnguyen , Change the return like this example

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

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

Helpful resources

Announcements
Fabric Community Conference

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.