Complex DAX formula needed

[MarkCBB]

Hi there,

I have 2 tables, Answer Table and Store Detail Table.

Answer Table:
This table consists of various answers to questions (200 questions per Store) as well as a Store ID that is used to create a relationship to the Store Detail Table.
There is a column called Question type i.e. H1, H2, H3, H4 etc.. as well as a column called Company Type, i.e. Own, External as well as a column called Answers.

Store Detail Table:
Contains the ID of the store (For the relationship) as well as the details about the store, i.e. store name, address etc…

My challenge.
I was able to create a DAX formula to calculate the % of Own H1 answers, below is the formula.
Dettol Soap = CALCULATE(SUM('RB DATA'[ANSWER-NUMERIC]),'RB DATA'[CHECKLIST]="SOS",'RB DATA'[LSM_TYPE]="HLSM",'RB DATA'[SCORECARD ITEM]="H1",'RB DATA'[TYPE]="OWN")/CALCULATE(SUM('RB DATA'[ANSWER-NUMERIC]),'RB DATA'[CHECKLIST]="SOS",'RB DATA'[LSM_TYPE]="HLSM",'RB DATA'[SCORECARD ITEM]="H1")
The above calculates the % of Own H1 out of Total H1.

The next step that I am struggling with is that I need to work out the % of Stores that are compliant, the compliance % is 30% so for example:
If OWN H1 = 40 and TOTAL H1 = 100 (Thus EXTERNAL H1 = 60). 40/100=40% so H1 is compliant.
I now need to calculate the percentage of Stores that are compliant. i.e. if there are 200 stores and 100 of them scores over 30% for H1 the Compliance percentage would be 50%.

I really hope the above makes sense, please shout if there is anything that I can explain better.

 

[Matt Allington]

You can use an iterator to work through the stores one at a time and then add up the total that pass the test.

Something like this.

=DIVIDE(sumx(Stores[StoreID],if([my % compliance] >=0.3,1,0)),[Total Number of Stores])

 

[MarkCBB]

Thank you Matt,

I was able to use your tips to solve my challenge.

Thank you