Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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%.
Solved! Go to 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
Proud to be a Super User!
Hi @Anonymous
Are you able to share data in text format?
Proud to be a Super User!
Part Number | Feature Number | Defect Rate (%) |
Part 1 | Feature 1 | 6.58 |
Part 1 | Feature 2 | 6.73 |
Part 1 | Feature 3 | 9.33 |
Part 1 | Feature 4 | 5.74 |
Part 1 | Feature 5 | 16.48 |
so just to be clear the values in the defect rate must total 10%
Proud to be a Super User!
I am not sure if this is what you are lookign for but see pbix attached.
Proud to be a Super User!
@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?
Proud to be a Super User!
@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?
Proud to be a Super User!
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
Proud to be a Super User!
@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?
Proud to be a Super User!
@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.
Proud to be a Super User!
@vanessafvg Does this help?
Part | Feature | Defects | Features | Defect Rate (%) | Total |
Part 1 | Feature 1 | 2 | 12 | 16.7 | |
Part 1 | Feature 2 | 4 | 10 | 40.0 | |
Part 1 | Feature 3 | 3 | 11 | 27.3 | |
Part 1 | Feature 4 | 1 | 24 | 4.2 | |
Part 1 | Feature 5 | 5 | 50 | 10.0 | 14.02 |
Part 2 | Feature 1 | 4 | 23 | 17.4 | |
Part 2 | Feature 2 | 6 | 43 | 14.0 | |
Part 2 | Feature 3 | 3 | 13 | 23.1 | |
Part 2 | Feature 4 | 2 | 16 | 12.5 | |
Part 2 | Feature 5 | 1 | 20 | 5.0 | 13.91 |
Part 3 | Feature 1 | 5 | 50 | 10.0 | |
Part 3 | Feature 2 | 6 | 90 | 6.7 | |
Part 3 | Feature 3 | 10 | 145 | 6.9 | |
Part 3 | Feature 4 | 3 | 20 | 15.0 | |
Part 3 | Feature 5 | 4 | 40 | 10.0 | 8.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.
Proud to be a Super User!
@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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |