After pivoting the date you provided I was able to return the first Frequency value for which each Publisher exceed the 80% KPI value. I attached a sample workbook that illustrates the solution below.
- In the Data Connection window, ctl+click the publisher columns, right click and select Pivot.
- Create a calculated field named Exceeded KPI using the formula: WINDOW_MAX(RUNNING_SUM(IF SUM([KPI]) > .8 THEN 0 ELSE 1 END) + 1)
- Create another calculated field named Index Filter using the formula: INDEX()=1
- Place Publisher on Columns, Frequency on Rows, Exceeded KPI on Text, and Index Filter on Detail
- Right click Exceeded KPI and Index Filter on the view and select Compute Using > Frequency
- Move Index Filter from Detail to the Filters shelf, select True, and click OK
- Move Frequency from the Rows shelf to Detail.
In the attached workbook, I broke out Exceeded KPI calculation into three separate fields so you can see how the formula is working.
I am sure there is a prettier solution out there, but I hope this helps in the meantime! Let me know if you have any questions about these steps.
ExceededKPI.twbx 100.4 KB
you're solution does the magic.
Thanks a lot.
It's perfect for my actual needs.
I didn't know about the Pivot function, and I think I'm gonna definitely learn more about it, since it seams it can came in handy in a lot of situations.