Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Percentages

How can I calculate the average of percentage grouped by category based on a criteria?

 

In this case, the grouping is by "Machine" and the percentage is number of failed products to total products by machine. Then the average of percentage of failed products over all machines.

 

My current formula:

AVERAGEX(
    KEEPFILTERS(VALUES(Table[Machine])),
DIVIDE( CALCULATE(SUM(Table[Volume]),Table[Quality] = "Fail") , CALCULATE(SUM(Table[Volume]),ALL(Table[Machine])) )
)

 

Data aka "Table":

MachineQualityVolume
1Fail5
1OK7
1Good2
2Fail4
2OK5
2Good3
3OK1
3Good8

 

What I want:

Machine% Failed
10.36
20.33
30.00
Average0.23

 

The issue is that since Machine 3 did not produce any failed items, it gets "filtered out". How can I make sure machine 3 gets included? That is, the final calculation should be (0.36+0.33+0)/3 = 0.23, rather than (0.36+0.33)/2 = 0.35.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

     

    %Failed = AVERAGEX(
    KEEPFILTERS(VALUES('Table'[Machine])),
DIVIDE( CALCULATE(SUMx('Table',if('Table'[Quality] = "Fail",'Table'[Volume],0))) , CALCULATE(SUM('Table'[Volume])) )
)

     

    pbix : https://www.dropbox.com/s/an0ry8twt8m9hrw/machineData.pbix?dl=0

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks amitchandak, adding the IF made it work like a charm!

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You could refer to the following DAX:

    Measure =
VAR a =
    CALCULATE (
        (
            CALCULATE (
                SUM ( 'Table'[Volume] ),
                FILTER ( 'Table', 'Table'[Quality] = "Fail" )
            ) + 0
        )
            / SUM ( 'Table'[Volume] ),
        ALLEXCEPT ( 'Table', 'Table'[Machine] )
    )
RETURN
    a

    Here is the result.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This works in this regard, but I think amitchandak 's solution is more flexible, since I can pull in other filters to further slice-and-dice the data as needed.