Calculate percentage of category excluding some value from denominator

[Jacekk111]

Greetings Community.

I'm looking for a solution to calculate % by category. However I have constraints that I must subtract from denominator one of category for all. 

I'm dividing Pomiar1/Pomiar2

Pomiar1 = SUM(Zbiorcza[crfea_czas])

Pomiar2 = SUM('Open Time'[CzasOpen])-CALCULATE(SUM(Zbiorcza[crfea_czas]),Zbiorcza[crfea_rodzajstraty]="No Production/ Brak Produkcji")

My problem is that time "No Production" is not substract for each category in Pomiar2 (2880 should - 1467 for each) but only for categor "No Production/ Brak Produkcji") and wrong % is calculated at the end. Any ideas how to improve?

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[v-easonf-msft · Accepted solution]

Hi, @Jacekk111 

Try to modify 'Pomiar2' formula like:

Pomiar2 =
SUM ( 'Open Time'[CzasOpen] )
    - CALCULATE (
        SUM ( Zbiorcza[crfea_czas] ),
        FILTER (
            ALL ( Zbiorcza ),
            Zbiorcza[crfea_rodzajstraty] = "No Production/ Brak Produkcji"
        )
    )

QE_Kategorie=Pomilar1/Pomiar2

If it doesn't work, please share a sample pbix for further research.

Best Regards,
Community Support Team _ Eason