Selecting a group by one value and creating an average

[Jwit]

I'm fairly new to Power BI and writing dax, but reading answers in this forum has helped me a lot.  So, faced with a new problem I can't work out I thought I'd ask for some help.

I have a spreadsheet of data that records answers (yes/no) regarding shortages in many facilities.  Each facility is asked about 8 types of shortages.  I am trying to calculate out what the percentage of facilities that have shortages is.  I wrote a simple measure that does this:

% YES today =

DIVIDE (

 CALCULATE ( COUNT ( Table1[shortage]), 'Table1'[shortage] = "YES" ),

 CALCULATE ( COUNT ( Table1 [shortage] ), ALLSELECTED ( Table1[shortage] ) )

)
However, I then realized that one of the things I want to know is the percentage of facilities that have any type of shortage. So if my
data looked like:

Facility Name	type	Shortage
Facility 1	A	no
Facility 1	B	no
Facility 1	C	yes
Facility 2	A	no
Facility 2	B	no
Facility 2	C	no
Facility 3	A	no
Facility 3	B	no
Facility 3	C	no

My measure returns an average of 11%, but I need it to be 33% as one of the three facilities has a shortage of type C.
I am just not sure how to group the facilites by name and then have any yes designate that facility as yes overall.

Any help is appreciated, thanks in advance.

[mahoneypat]

The measure below should return a result of 33%

% Facilities with Shortage =
VAR shortagefacilities =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table1[Facility] ),
            NOT (
                ISBLANK (
                    CALCULATE (
                        COUNT ( Table1[shortage] ),
                        'Table1'[shortage] = "YES"
                    )
                )
            )
        )
    )
VAR facilities =
    COUNTROWS (
        ALLSELECTED ( Table1[Facility] )
    )
RETURN
    DIVIDE (
        shortagefacilities,
        facilities
    )

Regards,

Pat

[Jwit]

@mahoneypat 

For some reason this measure seems to give me the exact same percentage for every facility in my data.  I'll have to dig in to the data and see why that might be happening.

[Ashish_Mathur]

Hi,

What result would you expect (share the numerator and denominator speprately), if your data was like this (my edited cells are highlighted in yellow)

[Jwit]

@Ashish_Mathur 

I would expect this result to have a numerator of 2 and a denominator of 3 so 2 of 3 facilities would now have shortages.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jwit]

@Ashish_MathurThis is exactly what I was looking for, thank you very much!

In case anyone else is wondering it was a three measure combination of:

Count of facilities = DISTINCTCOUNT(Data[Facility Name])

Facilities with shortages = CALCULATE(COUNTROWS(Data),Data[Shortage]="Yes")

Measure = COUNTROWS(FILTER(VALUES(Data[Facility Name]),[Facilites with shortages]>0))/[Count of facilities]

[Ashish_Mathur]

You are welcome.