Require some pointers and examples please

[#1]

I am working on a demo app as below that basically shows the current Risk and exposure of an Insurance company - offset by the quality of the data underpinning the report .. made up of a mixture of the raw data tables and a feed from a data quality tool.

All working as expected currently (I think!!)

I want to be able to show the worst (top) offender for Data Quality by both the brokerage and the agent responsible for the payments - as the user selects various time segments or products etc. Data model below as well...

So ..App

Data Model

Each Fact table has a compliance score - Pass of Fail

So if a user is looking at 2013 - Q2 for Travel Insurance -- I want to see the broker who has the highest failing compliance count of Claims + Policy + Payments.. and like wise for the Agent.

Any pointers would be most appreciated.

[sujeetsingh]

Did not get you

[#3]

Sorry .. so a little simpler ..

regard less of what selection the user makes .. by Product/type/date

I want to show the broker with the highest count of failing records within that selection. Rank no 1?

[#4]

So far I have this in the text box - but it is giving me a null/empty value

=only({<[Broker Name]={"=rank(Count ({<[Claims Compliance]={'Fail'}>}[Claims Compliance])+ Count ({<[Payments Compliance]={'Fail'}>}[Payments Compliance])+Count ({<[Policy Compliance]={'Fail'}>}[Policy Compliance]),[Broker Name])) =1"}>}[Broker Name])

[#5 · Accepted solution]

Fixed the problem ...

=FirstSortedValue([Broker Name],- aggr(Count({1<[Claims Compliance]={'Fail'}>}[Claims Compliance])+ Count ({<[Payments Compliance]={'Fail'}>}[Payments Compliance])+Count ({<[Policy Compliance]={'Fail'}>}[Policy Compliance]), [Broker Name]) )