Calculating Percentages on a field that has multiple values

[mfminor]

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.

[DataChant]

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 %.

[mfminor]

What 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.

[Anonymous]

Hi @mfminor,

You can try to use max function to get the calculated result of current content.

% of failures = FORMAT(DIVIDE(Max([formula count]),Max([total produced]),0),"Percent")

Regards,

Xiaoxin Sheng

[DataChant]

Any 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.

[mfminor]

Unfortunately I do not think I can. Our IT department gets kind of sensative about sharing some of our data. I would probably agree that restructuring some of the data is in our best interest. That being said, I do not have ownership of any of the tables and I am unable to modify them. The information I am gathering is coming from several departments within the aviation segment, which further complicates having changes made. I can explain that I get the data by running a query in Access then dumping it into Excel unmodified then link Power BI to the Excel workbook. I tried to link directly to the Access tables I use, but I run into some issues. I think some of this comes from the fact that some are on SQL servers. Here is a couple screen shots that may help find a solution.

Design View of the query I run.After pulled out of Excel I relate the tables as shown.

[mfminor]

I found a way to get it to work. I created a copy of the AviationTable and grouped by the unit description and total produced. I was then able to relate the tables using the unit description. However this is not perfect I now have a resulting question. I imagine this will be easy to solve but I am new to DAX and Power BI. Currently the calculation shows in 0.00 format. I want to see it in 0.00000 at least if not a 0.000%.  

Number of Failure vs Total Produced = CALCULATE(DIVIDE(AviationTable[Formula Count], max(GroupedbyUnitDescription[total produced]), "no result"))

This is the formula I am using currently.

This is the table and how it looks now. I see a lot of 0.00 which provides no value to me since I expect to see values that do not equal a full percent.

[mfminor · Accepted solution]

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

[mfminor]

I was able to filter out all the weird results and not disturb the actual results. I am now satisfied with the results I have. I still think that my previous post with the weird "anomaly" that occurred could be addressed.