Count of top N% of total distinct Member Id order by Amount paid

[Anonymous]

Hi Everyone,

I have a requirement on which I am stuck and scratching my head to find a solution to it.

Power BI community have always helped to reach my solutions.

Any help on below mentioned requirement would be really appreciated.

Requirements:

• I have to calculate count of Top 1%, Top 5%, Top 20% and Others of total Distinct Member Id order by Paid amount
• I then have to calculate above mentioned point in sync with two slicers, One slicer of ClientName and other slicer of date(only years), where that date slicer should calculate total number of members active in that year and amount paid in that year.
• At last, I have to display the pie chart showing amount paid by top 1%, top 5%, top 20% and others and then do drill through to see those 1% or 5% member details

Steps I performed so far:

• I have Two tables, one gives me member details and other table gives me amount paid where the relationship is by member key. I created a summarize table where I have member details, paid amount and date in one table.
• I then created rank by calculated columns but somehow it does not give me accurate rankings when I select clientname filter
  Rank1 = RANKX (
  FILTER ( 'Summarize', EARLIER( 'Summarize'[ClientName]) = 'Summarize'[ClientName]),
  VALUE ( 'Summarize'[Totalamount] ),
  ,
  DESC,
  Skip
  )
• Created categories 1%, 5%, 20% where it gives me the count of total distinct member count, but the count doesn’t change if I put date slicer in report.

Sample Data:

Table 1: Member table

ClientName	HCHMemberID	EffectiveDate	TermDate	MemberKey
CNB	abc1234	1/1/2018 0:00	12/31/2999 0:00	12
CNB	asd 324	3/1/2019 0:00	12/31/2999 0:00	13
CNB	bn666	1/1/2018 0:00	3/31/2018 0:00	22
THE	rfe762	4/1/2019 0:00	3/31/2018 0:00	23
THE	kiu765	1/1/2019 0:00	12/31/2999 0:00	45
THE	mli987	2/1/2019 0:00	12/31/2999 0:00	64
ABC	buh437	10/13/2018 0:00	3/31/2018 0:00	74
ABC	chw234	11/19/2018 0:00	12/31/2999 0:00	69
ABC	htt650	10/4/2018 0:00	12/31/2999 0:00	88
XZY	nvw231	1/1/2018 0:00	3/31/2018 0:00	77
XZY	zmn778	4/12/2019 0:00	3/31/2018 0:00	44
XZY	tdf555	1/1/2018 0:00	12/31/2999 0:00	29

Table 2: Fact table

MemberKey	Amount paid	Paid date
12	8900	12/10/2018
13	7650	3/10/2019
22	6400	6/19/2018
23	5373	4/23/2019
45	10000	2/10/2019
64	9876	3/13/2019
74	7654	12/10/2018
69	4567	12/10/2018
88	765	12/10/2018
77	45	6/19/2018
44	99	5/28/2018
29	100	12/10/2018

• Expected output
  
• Would really like to see ideas from you guys
  Let me know if I am clear enough with my question.
  
  Thank you so much for your help in advance.

[v-piga-msft]

Hi @Anonymous ,

If I understand your scenario correctly that you could create the measures below to achieve your desired output.

rank =
RANKX (
    ALLSELECTED ( 'Member' ),
    CALCULATE ( MAX ( 'Fact'[Amount paid] ) ),
    ,
    DESC
)
count_of id =
CALCULATE ( DISTINCTCOUNT ( 'Member'[HCHMemberID] ) )
count_of top1% =
VAR t1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Member'[HCHMemberID] ),
        FILTER ( 'Fact', 'Fact'[rank] <= 1 )
    )
RETURN
    t1 / [count_of id]

count_of top5% =
VAR t5 =
    CALCULATE (
        DISTINCTCOUNT ( 'Member'[HCHMemberID] ),
        FILTER ( 'Fact', 'Fact'[rank] <= 5 )
    )
RETURN
    t5 / [count_of id]

count_of top20% =
VAR t20 =
    CALCULATE (
        DISTINCTCOUNT ( 'Member'[HCHMemberID] ),
        FILTER ( 'Fact', 'Fact'[rank] <= 20 )
    )
RETURN
    t20 / [count_of id]

Then you could get the pie chart below.

More details, you could refer to my attachment.

If I misunderstood, please let me know.

Best  Regards,

Cherry

[Anonymous]

Thank you Cherry for your reply, appreciate your help.

We climbed a step here, but not there yet.
Firstly, when we say Top 1%, Top 5%, Top 20% and Rest it means we want count of distinct members according to percentage and order by amount paid. For example, if there are 1000 hchmemberid, Top 1% would give Top 10 members with highest amount paid, Top 5 % would give (1000 - 10)* 0.05 i.e 50 members with highest amount paid, Top 20 % would give (990 - 50)* 0.20 i.e 188 members with highest amount paid and the remaining members will be in others bucket and the count of member would change dynamically by the user using the slicer.

Secondly, the catch here are the two date slicer i.e effective date and paid date, when I select date slicer and clientName slicer (it will be in years like 2016,2017,2018) it should give us the count of members active  in that year and amount paid by member in that year. (In Term Date coulmn 12-31-2999 means member is active and any other date besides it means member is terminated)

Here we can either use two slicer of date and one slicer of Client name or One slicer of date (which filters date from both date columns) and one slicer for Client Name.

If you want me to give you a more bulky sample data set please do let me know.
Also, Let me know if I am making any sense here.

Would like to hear back from you.

Thanks for your time 🙂