topic Total Overall Value and Individual Value using the same Measure. How to divide them in DAX Commands and Tips

Total Overall Value and Individual Value using the same Measure. How to divide them

rocky_puff — Tue, 09 Jan 2024 09:26:27 GMT

Hi All.

I'm kind of stuck here. Need your help on this.

My problem is the measure used to get defect count and total defect count is the same, to differentiate it is using Plant (a column). I want to calculate defect count weightage as per formula below:

Defect count weightage = defect count / total defect count

The total defect count is the row that I highlighted in the screenshot. How to get that value if it's using the same measure?

Here is the measure used to calculate the defect count.

DefectCount = SUMX(FILTER(Joined_FactAIVC,Joined_FactAIVC[DefectClass]<> "Produced Glove" && Joined_FactAIVC[DefectClass]<> "Good Glove" && Joined_FactAIVC[DefectClass]<> "Empty Link" && Joined_FactAIVC[DefectClass]<> "System Status"),[Value])

Kindly help.

Re: Total Overall Value and Individual Value using the same Measure. How to divide them

lbendlin — Thu, 11 Jan 2024 00:18:59 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Total Overall Value and Individual Value using the same Measure. How to divide them

rocky_puff — Thu, 11 Jan 2024 07:57:07 GMT

ID	Defect Count	Defect Count Weightage
A	767,212	0.3848
B	504,089	0.2528
C	722,384	0.3623
Total	1,993,685

Re: Total Overall Value and Individual Value using the same Measure. How to divide them

rocky_puff — Thu, 11 Jan 2024 07:59:13 GMT

Above is the sample data that can be used.

My objective is to get the correct DAX to calculate defect count weightage.

Here is the formula

Defect count weightage = defect count / total defect count

Example,

for ID A : 767,212 / 1,993,685 = 0.3848

My current DAX,

DIVIDE(CALCULATE([DefectCount]),CALCULATE([DefectCount],ALL(Joined_FactAIVC))

which resulting to wrong output as below. The expected output is the same as the sample data above.

Re: Total Overall Value and Individual Value using the same Measure. How to divide them

lbendlin — Thu, 11 Jan 2024 13:59:20 GMT

Not sure where you are going with this. Here is the standard approach

But it feels like you need something different?

Re: Total Overall Value and Individual Value using the same Measure. How to divide them

rocky_puff — Mon, 15 Jan 2024 02:52:57 GMT

Yaa I think this solved it. I chose different type of ALL previously.

DIVIDE(CALCULATE([DefectCount]),CALCULATE([DefectCount],ALLSELECTED(TableABC[ID])))