Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How Do I Use DAX to Show a % of the Subtotal

I've tried to follow a few solutions online to show a % of a subtotal within my dataset but I can't get it to work for my example. 

 

image001.png

 

The table above shows the defects per feature for part 1. The total defect rate for part 1 is 10% (10 out of 100 features have a defect) and it is split into features each with their own defect date. 

 

My question is: how can I use DAX to display the defect rate as a percentage of the subtotal for Part 1 so when the column is added together sum is 10%. 

1 ACCEPTED SOLUTION

thanks, i think we might be miscommunicating.   the total defects add up to 100% i get that.  Have you looked at the solution  I provided?   

 

I am not sure we are on the correct page.   Are you wanting to undertand the % of features as a % of the parts?

Please see solution and give feedback on the solution as your requirement are not clear on what your expected output is.  please provide an actual example of what output you require so that its clear.  I don't understand where the 10% comes in.

ie 

vanessafvg_0-1634812330301.png

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

19 REPLIES 19
vanessafvg
Super User
Super User

Hi @Anonymous 

 

Are you able to share data in text format?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg 

 

Part NumberFeature NumberDefect Rate (%)
Part 1Feature 16.58
Part 1Feature 26.73
Part 1Feature 39.33
Part 1Feature 45.74
Part 1Feature 516.48

so just to be clear  the values in the defect rate must total 10%





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg yes - for part 1 the subgroup size for the defect rate is 10%

I am not sure if this is what you are lookign for but see pbix attached.

 

New Defect Rate =
var tot = CALCULATE('Table'[Total], all('Table'))
var selval = SELECTEDVALUE('Table'[Defect Rate (%)])
return format(divide(DIVIDE(selval,tot),10), "Percent")




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg Thank you. I will check it out in the morning. Will this work for all the part numbers I have in my dataset - each with a different defect rate?

will there be different part numbers to part number 1 and do those part numbers also all need  to add up to separately each 10%?   or is it just part number 1?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg Yes - for example part 2 has a defect rate of 4% and part 3 has a defect rate of 3.8%. Sorry I can't share the real dataset - there is too much confidential data within it to screen it out.

in order to calculate this correctly you would need to provide all the data ratios that make up the 100% as there is more than one value for part one and assuming there is more than one value for part 2 and they dont seem to add up all to 10% so its not clear how you want this worked out.  I think you first need  to look at the solution and see where its wrong

 

maybe give a list of all the part numbers and what total % is for each part.  Wat are you trying to do froma business perspective so I can understand better?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

thanks, i think we might be miscommunicating.   the total defects add up to 100% i get that.  Have you looked at the solution  I provided?   

 

I am not sure we are on the correct page.   Are you wanting to undertand the % of features as a % of the parts?

Please see solution and give feedback on the solution as your requirement are not clear on what your expected output is.  please provide an actual example of what output you require so that its clear.  I don't understand where the 10% comes in.

ie 

vanessafvg_0-1634812330301.png

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg Apologies, that was my fault trying to create too simple a data set as an example at the start. The example file you attached is really close to the solution that I'm looking for. However, I need to understand the VAR tot function. What it is designed to do because when I transfer it over to my real dataset I'm getting a completely different number.

 

thanks for all your help so far.

ah ok. So when you say you getting a totally diffferent number, is all the data you are using in the same table.  The key would be what are the differences between what you provided me vs what you are trying to do in your model. 

 

I know your data is confidential, but if you have more than one table that has relationships, that would be relevant and also what the make up of your data is.  So when you say you getting a different number  what are you getting.  What is different in your actual data?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg All my data is in one table however there are multiple rows per feature which are either bad or good. I've used a measure the calculate the 'bad' for the defects column. I can't work out where this 'different' number is coming from.

Hi @Anonymous 

 

Can you let me know if my below understanding and assumptions are correct or not? Cause I guess your [Total Measurements] and [Defects] column are calculated from a raw table which has good/bad values for every measurement. 

 

For every feature in a part, it has many measurements. Every measurement is on a row and it has a good/bad value on this row to tell whether there is a defect. If a row has "bad" value, we think this measurement has a defect. So for every feature, its defect rate is (number of "bad" measurements)/(number of all measurements under this feature).

 

When it comes to calculate the subtotal of defect rate for a part, do we only need to count how many features have at least one defect without the need to know how many measurements have defects? For example, if Part 1 has 10 features (total 100 measurements) and 5 of them has total up to 20 "bad" measurements. The other 5 features don't have defects. We should divide 5 by 10 ((number of "bad" features)/(number of all features under a part)) to get the subtotal defect rate? Or divide 20 by 100? Or divide 5 by the number of all features of the whole product?

 

Best Regards,
Community Support Team _ Jing

so you have a column that has either a value of good or bad in it?   and you filter on this column?   Can you add that to the data you provided me with in the excel?  because the total  is just a sum of all the defects.  If you need to only view bad defects you will need a total that only calculates the bad defects.  Dunno if that makes sense.   

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg Does this help?

 

PartFeatureDefectsFeaturesDefect Rate (%)Total
Part 1Feature 121216.7 
Part 1Feature 241040.0 
Part 1Feature 331127.3 
Part 1Feature 41244.2 
Part 1Feature 555010.014.02
Part 2Feature 142317.4 
Part 2Feature 264314.0 
Part 2Feature 331323.1 
Part 2Feature 421612.5 
Part 2Feature 51205.013.91
Part 3Feature 155010.0 
Part 3Feature 26906.7 
Part 3Feature 3101456.9 
Part 3Feature 432015.0 
Part 3Feature 544010.08.12

not really because I still dont know how it is different to what you have?

 

you said;

All my data is in one table however there are multiple rows per feature which are either bad or good. I've used a measure the calculate the 'bad' for the defects column. I can't work out where this 'different' number is coming from.

 

You have not shown me how you calculate the good or bad and how this is relevant.  

please can you give me an example of your expected output? Unfortunately I am still not understanding clearly what is wrong bcause you haven't shown what you expect to come out with so i have no idea how to fix what I have done.

 

Please provide the example of what you want the solution to look like.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg I realised the difference in numbers came from having filters set to all pages. I'll have to figure out a different way to apply these but your solution worked! Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.