Summarize Compliance of Shopping Malls by Row Level Using Measure

[Adaz]

Hello,

 I am trying to summarise the performance of kiosks within shopping malls using a measure. If a Kiosk Type is existing plus clean then this is considered a compliant Kiosk Type within a mall.

For example Kiosk Type A in Sunnyside Shopping Mall is compliant as it exists and clean.

However Kiosk Type B within the same Sunnyside Shopping mall is not compliant as the second Kiosk Type B (Kiosk TypeB2) is not clean.

My data looks like the following.

Mall Unique Code	Shop Name	Kiosk Type	Kiosk Type Unique Code	Question	Answer
1	Bayswater Shopping Mall	KIOSK Type A	Kiosk Type A	Does this exist?	No
1	Bayswater Shopping Mall	KIOSK Type A	Kiosk Type A	Is this clean?	No
1	Bayswater Shopping Mall	KIOSK Type B	KIOSK Type B	Does this exist?	Yes
1	Bayswater Shopping Mall	KIOSK Type B	KIOSK Type B	Is this clean?	Yes
1	Bayswater Shopping Mall	KIOSK Type B	KIOSK Type B2	Does this exist?	Yes
1	Bayswater Shopping Mall	KIOSK Type B	KIOSK Type B2	Is this clean?	Yes
2	Sunnyside Shopping Mall	KIOSK Type A	Kiosk Type A	Does this exist?	Yes
2	Sunnyside Shopping Mall	KIOSK Type A	Kiosk Type A	Is this clean?	Yes
2	Sunnyside Shopping Mall	KIOSK Type B	KIOSK Type B	Does this exist?	Yes
2	Sunnyside Shopping Mall	KIOSK Type B	KIOSK Type B	Is this clean?	Yes
2	Sunnyside Shopping Mall	KIOSK Type B	KIOSK Type B2	Does this exist?	Yes
2	Sunnyside Shopping Mall	KIOSK Type B	KIOSK Type B2	Is this clean?	No

The output I desire is below in this summary table.

Kiosk Type	Number of Compliant Malls
KIOSK Type A	1
KIOSK Type B	1

Kiosk Type A is compliant in Sunnyside Shopping Mall.  (clean and existing)

Kiosk Type B is compliant in Bayswater Shopping Mall as both Kiosk Type Bs are compliant. (clean and existing)

My attempt

Measure 1

Kiosk Count

=
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[Question] = "Does this exist?",
    Table1[Answer ] = "Yes",
    ALLEXCEPT ( Table1, Table1[Mall Unique Code], Table1[Kiosk Type] )
)

Measure 2

Clean Count=
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[Question] = "Is this clean?",
    Table1[Answer ] = "Yes",
    ALLEXCEPT ( Table1, Table1[Mall Unique Code], Table1[Kiosk Type] )
)

Measure 3

DIVIDE([Kiosk Count],[Clean Count])

---------

If I pivot the Kiosk type and the second heirachy is by mall and I count the 1s (which represent 100%). I get the result I desire (just not in the above summary table)

I am trying to avoid using calculated columns and was wondering if it is possible to roll up the compliance using measures only in the summary table?

Any help would be appreciated.

Thank you all!

[Adaz]

Any help would be appreciated

[Adaz · Accepted solution]

I think I figured this one out.

The following measure provides the result I am after

Number of Compliant Malls:=CALCULATE(DISTINCTCOUNT('Table1'[Mall Unique Code]),FILTER('Table1',[Measure 3]>=1))

If anyone has a different way of calculating this, I would be interested in hearing this.

Thank you all.