Top 6 + Other for each month

[PowerOn_DP]

Hi,

I am right on my first power BI report, trying to build SSRS version in pbi.

Please check below supported datasets 

1] Data looks like below in actual table

FirstContactOn	Club_Code	Location	Source_Type	New_Prospect_ID	START_ON	ARCHIVED
01-12-2020	8608	Colchester AGCO	Online Membership Enquiry	1	12-11-2012	1753-01-01
01-12-2020	8610	Edgbaston AGED	Online Guest Pass	2	01-03-2013	1753-01-01
01-12-2020	8625	Dun Laoghaire	Google	2	01-09-2013	1753-01-01
01-12-2020	8633	High Wycombe	Social Media	1	22-02-2014	1753-01-01
01-12-2020	8636	Stratford	Online Membership Enquiry	1	15-05-2017	1753-01-01
01-12-2020	8636	Stratford	Web site	1	15-05-2017	1753-01-01
01-12-2020	8639	Telford	Friend Or Family	3	01-05-2014	1753-01-01
01-12-2020	8639	Telford	Online Membership Enquiry	1	01-05-2014	1753-01-01
01-12-2020	8644	Kensington	Web site	1	01-11-2014	1753-01-01
01-12-2020	8647	Clapham SouthAGCS	Facebook	1	01-11-2014	1753-01-01
01-12-2020	8650	Ilkeston	Corporate Deal	1	13-04-2015	1753-01-01
01-12-2020	8655	Gosport AGGP	Facebook	2	01-06-2015	1753-01-01
01-12-2020	8660	Bicester AGBC	Online Guest Pass	2	10-08-2015	1753-01-01
01-12-2020	8663	Tooting	Web site	1	25-11-2015	1753-01-01
01-12-2020	8677	Grantham	Online Guest Pass	1	05-09-2016	1753-01-01
01-12-2020	8678	Trowbridge	Friend Or Family	1	16-01-2016	1753-01-01
01-12-2020	8678	Trowbridge	Online Membership Enquiry	2	16-01-2016	1753-01-01
01-12-2020	8692	Dalston	Online Guest Pass	1	12-10-2018	1753-01-01
01-12-2020	8709	Worthing	Online Membership Enquiry	1	01-02-2017	1753-01-01
01-12-2020	8733	Letchworth	Online Membership Enquiry	1	05-12-2016	1753-01-01
01-12-2020	8745	Swansea	Referral	1	05-12-2016	1753-01-01
01-12-2020	8755	Chorley	None	1	02-01-2017	1753-01-01

2] For the SSRS, I had a sql code using which I had returned below output for my chart 

Datefrom	MonthYear	Rn	Source_Type	New_Prospects
01-11-2020	Nov-20	1	Online Membership Enquiry	881
01-11-2020	Nov-20	2	Leaflet	786
01-11-2020	Nov-20	3	Online Guest Pass	495
01-11-2020	Nov-20	4	Friend Or Family	447
01-11-2020	Nov-20	5	None	383
01-11-2020	Nov-20	6	Facebook	380
01-11-2020	Nov-20	7	Other	1312
01-12-2020	Dec-20	1	Online Guest Pass	2763
01-12-2020	Dec-20	2	Online Membership Enquiry	2202
01-12-2020	Dec-20	3	Friend Or Family	1726
01-12-2020	Dec-20	4	None	1524
01-12-2020	Dec-20	5	Web site	761
01-12-2020	Dec-20	6	A Friend	719
01-12-2020	Dec-20	7	Other	6118

Now as I had this resultset, it was easy to create a stacked bar chart by putting source type on axis and sum up New prospects for each month like below 

Now, If I want you to walk through the sql where I am creating rank and the Other group with all the source types coming after Rn=6 

Coming back to Power Bi, I have added the table which is showing in point 1]

I have Club and Calender master tables in relationships to have slicer on Club_Code and FirstContactOn 

Now, I have tried summarize table using dax, measures, calculated columns and so many things to group the data using Source type and sum New_Prospects_ID for each month calculated using FirstContactOn,  but I am not able to get the expected data with transformation in power bi shows in point 2] 

Its been 2 days but it just makes me confused after trying many things and thus still standing at the same page. 

Please any help would be appreciated. Please ask for any supporting script if needed.

Thanks in advance! 

[nandic · Accepted solution]

Hi @PowerOn_DP ,

Here is formula to rank Sum of New_Prospect_ID grouped by FirstContactOn and SourceType.

Rank NewProspectID by Source_Type and FirstContactOn =
VAR RankProspectsBySource =
    RANKX (
        ALL ( 'Prospects' ),
        CALCULATE (
            SUM ( 'Prospects'[New_Prospect_ID] ),
            ALLEXCEPT ( 'Prospects', 'Prospects'[Source_Type], 'Prospects'[FirstContactOn] )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( RankProspectsBySource <= 6, RankProspectsBySource, 7 )

ALLEXCEPT (dax) = GROUP BY (sql)
RankX = in expression it should be some calculation if needs summarized version, not only column name



Regards,
Nemanja Andic

[Icey · Accepted solution]

Hi @PowerOn_DP ,

How about creating a calculated column like below?

Rabk TopN+Other =
VAR RankProspectsbySource =
    RANKX (
        FILTER (
            Prospects,
            Prospects[FirstContactOn] = EARLIER ( Prospects[FirstContactOn] )
        ),
        CALCULATE (
            SUM ( Prospects[New_Prospect_ID] ),
            ALLEXCEPT ( Prospects, Prospects[Source_Type], Prospects[FirstContactOn] )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( RankProspectsbySource <= 6, RankProspectsbySource, 7 )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[PowerOn_DP]

Hi,

I tried Rankx with if condition to just see how the rank will be generated for my Source Type based on Total of New_Prospects_Id

Now, I just picked up Rank TopN+other into table with Source category and one measure (Does sum of New Prospects ID)

Can you please help me why the rank looks weird

[nandic · Accepted solution]

Hi @PowerOn_DP ,

Here is formula to rank Sum of New_Prospect_ID grouped by FirstContactOn and SourceType.

Rank NewProspectID by Source_Type and FirstContactOn =
VAR RankProspectsBySource =
    RANKX (
        ALL ( 'Prospects' ),
        CALCULATE (
            SUM ( 'Prospects'[New_Prospect_ID] ),
            ALLEXCEPT ( 'Prospects', 'Prospects'[Source_Type], 'Prospects'[FirstContactOn] )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( RankProspectsBySource <= 6, RankProspectsBySource, 7 )

ALLEXCEPT (dax) = GROUP BY (sql)
RankX = in expression it should be some calculation if needs summarized version, not only column name



Regards,
Nemanja Andic

[PowerOn_DP]

Hi @nandic 

Thanks for sharing, I will check it and see if they can be of any help in custimizing.

[nandic]

Hi @PowerOn_DP ,

At this moment i can't provide final solution, but here are great examples how to make it and hope it will help you figure out the logic and if need to customize to your scenario:
1) Curbal: https://www.youtube.com/watch?v=UAnylK9bm1I (uses calculated column, so rank will be static - it will not change based on slicers change)
2) SQLBI: https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/ (uses measure so it will be dynamic if slicers change)
3) Community: https://community.powerbi.com/t5/Desktop/Top-N-and-others-at-the-same-time/td-p/493040 (uses also measure)

Regards,
Nemanja Andic