Forum Discussion
Helper ICalculating Percentages on a field that has multiple values
I have an issue where I am trying to get a percentage based on the number of returns of a specific unit / the total number of units produced. I have tried many different things to get this to work. All of my data for this calculation is in one table. My data is sourced from and Excel spreadsheet that is just a query from Access that has been dumped into it. My units produced number is based on the part number in the table, as is the model name (there are multiple part numbers for the same model). To get the number of returns I am using a measure that counts the number of times a specific model has been returned. Below is a pretty generic picture of what I am trying to do.
I realized that a simple solution was just to format the measure as a percentage. This fixed the issue with the formating but made my table massive in size adding several blank fields with really odd numbers for my total produced column. I have no idea where to go with this one, but here is a pic.
Number of Failure vs Total Produced = FORMAT(DIVIDE(AviationTable[Formula Count], max(GroupedbyUnitDescription[total produced]),"no result"), "percent")
8 Replies
- DataChant
Most Valuable Professional
You can add a second measure:
Count All Units = CALCULATE ( [Formula Count] , ALL ( AviationTable[Unit Description] ) )
And then add the following measure:
Unit % = DIVIDE ( [Formula Count] , [Count All] )
And change the format of the last measure to %.
mfminorWhat my goal is to see is:
formula count / total produced = % of failures
ex/ GWX68 101 4723 2.13%
calculated by 101/4723=0.0213...
The table I have in my original post shows the Total produced is not the same for every unit. This is where I am running into issues. I will usually get the error stating that there are multiple values where one value is expected, then Power BI suggests using a count, average, sum, min, max, etc. I almost need something similar to a vlookup that says where this unit description exists divide the [formula count] by [total produced] and display the results.
- DataChantAny chance you can share your sample data ina PBIX file? (upload to DropBox/OneDrive and share the link).
It is difficult to follow without real data. It seems you can benefit from re-modeling your data to fact table and connected lookup tables, that will simplify your DAX formulas.
