Cross Post:If condition for a powerpivot

[Balajisx]

Hi Team,

I have asked the same question in a different forum. link below

https://www.excelforum.com/excel-general/1190900-if-condition-for-a-range.html#post4689244

1. I have a raw data which contains the quality details like agent name, files audited ,pass or fail . I have converted this data to a table and added to data model.

2. Using simple DAX function, I have created measures for Volumes audited, Quality %, How many are critical, and How many or non critical errors.

3. By using the data model I have created a monthly summary pivot table in the summary tab of my file which shows the monthly qualilty scores for each individuals.

*******Now real scnerio is to find the Quality Improvement phase, by using the same data model, I have to build a pivot table which shows the latest 10 weeks quality score for each agents and I need to find which phase they are belongs to based on the below criteria. ****

QIP 1 - if the agent quality score fall below 95 % for consecutive 2 weeks then he will be in QIP 1

QIP 2 - if the agent quality score fall below 95 % for consecutive 5 weeks then he will be in QIP2

Call to Action : if the agent quality score fall below 95 % for consecutive 9 weeks then he will be in CTA.

I am not able to find a solution since it is in a powerpivot. Is there any way to do this?

Attached is the sample file for your reference.

Thanks in advance,

Regards,
Balajisx

 

[Balajisx]

Dear All,

is there any way to find the solution for my above question?

 

[Chihiro]

What you are looking to do is impossible with PivotTable, as per your data model and Pivot Table design.

If you are willing to accept formula solution. Do something like...
=IFERROR(CHOOSE(MATCH(MAX(FREQUENCY(IF(L8:T8<>"",IF(L8:T8<0.95,COLUMN(L8:T8))),IF(L8:T8>=0.95,COLUMN(L8:T8)))),{2,5,9},1),"QIP 1", "QIP 2", "Call to Action"),"")

Confirmed as array (CTRL + SHIFT + ENTER)

 

[Balajisx]

Thank you!! It is working fine