Filter Top N% Column Values by Other Column Values

[Anonymous]

I am migrating one report from Excel to Power BI. So, in Power BI I need to implement the same Excel Filter shown in attached screenshot. Basically the logic needs to be like, the Values in Column C are to be summarized by Values in Column A and then the Values in Column B need to be filtered(Only Top 60% Values) by summarized Values of Column C.

NOTE: Column B has multiple Sub-Values for Each Value in Column A as you can see a (+) sign before each value in Column A 

Can someone help with the DAX Logic that need to be followed in order to achieve this functionality.

[parry2k · Accepted solution]

@Anonymous attached

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Many Thanks @parry2k for the solution. I'll try this now with the large dataset.

[parry2k]

@Anonymous not fully clear what you are looking for. can you provide an example of the output?

[Anonymous]

@parry2k The calcuation measures that you provided are correct. My current requirement is as you can see in the attached picture i.e. whatsoever Suppliers get filtered as Top N%(Ex: Top 60%), I can get a sum of their count in a new column as shown in the attached picture and this Sum of their count must come from Number Supplier Orders column of Table1 as for each distinct Supplier in the Supplier Column there is a value "1" in Number Supplier Orders column of Table1(Please Refer Table1 data)

[parry2k]

@Anonymous file attached for your reference, enjoy!!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k I think we are close to achieving the required functionality. Thanks Again.

Is it also possible to have a measure column in the same matrix visual which can show the Sum of Number of Corresponding Filtered Suppliers (Using Number Supplier Orders column from Table). Please refer attached screenshot.

[Anonymous]

@parry2k Thanks a lot, I'll apply the same dax logic and will let you know about the results that I will get with the actual data.

[smpa01]

Thanks @parry2k @AlexisOlson  for the quick response. @parry2k  will study it and might get back to you with any follow uo question I might have.

@Anonymous  guess you are sorted now.

[parry2k]

@Anonymous I think this will do it, I guess this is what you are looking for:

[parry2k]

@Anonymous Given the data, is this the expected output

[smpa01]

@Anonymous  

I don't think PBI has anything which utilizes % as TOPN

What you want can be returned through a DAX table function but can this be done with a measure. looping @AlexisOlson  and @parry2k  attached a pbix for your refrerence

DAX Table function that returns the derived table, but offcourse that is not desired. The foloowing DAX query returns if a the same PIVOT table was filtered in excel

Measure =
VAR _1 =
    ADDCOLUMNS ( 'Table 1', "_pct", [pct] )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "rank",
            RANKX (
                FILTER (
                    _1,
                    'Table 1'[Local Product Group Unit]
                        = EARLIER ( 'Table 1'[Local Product Group Unit] )
                ),
                [_pct],
                ,
                DESC
            )
    )
VAR _3 =
    ADDCOLUMNS (
        _2,
        "runningPct",
            SUMX (
                FILTER (
                    _2,
                    'Table 1'[Local Product Group Unit]
                        = EARLIER ( 'Table 1'[Local Product Group Unit] )
                        && [rank] <= EARLIER ( [rank] )
                ),
                [_pct]
            )
    )
VAR _4 =
    ADDCOLUMNS (
        ADDCOLUMNS ( _3, "is60", IF ( [runningPct] >= 0.6, 0, [runningPct] ) ),
        "is60_2", IF ( [runningPct] >= 0.6, [runningPct], 0 )
    )
VAR _5 =
    ADDCOLUMNS (
        _4,
        "rank2",
            IF (
                [is60] = 0,
                RANKX (
                    FILTER (
                        _4,
                        'Table 1'[Local Product Group Unit]
                            = EARLIER ( 'Table 1'[Local Product Group Unit] )
                    ),
                    [is60_2],
                    ,
                    asc,
                    DENSE
                )
            )
    )
VAR _6 =
    FILTER ( _5, [is60] <> 0 )
VAR _7 =
    FILTER ( _5, [rank2] = 2 )
VAR _8 =
    NATURALINNERJOIN (
        'Table 1',
        SUMMARIZE ( UNION ( _6, _7 ), [Local Product Group Unit], [Supplier] )
    )
RETURN
    _8

[AlexisOlson]

Can we use a measure as a visual level filter?

Where

runningPct =
VAR GroupSummary =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                'Table 1',
                'Table 1'[Local Product Group Unit],
                'Table 1'[Supplier]
            ),
            REMOVEFILTERS ( 'Table 1'[Supplier] )
        ),
        "@pct", [pct]
    )
VAR CurrPct = [pct]
RETURN
    SUMX ( FILTER ( GroupSummary, [@pct] >= CurrPct ), [@pct] )

[smpa01]

@Anonymous  sure no problem. Can you please provide some data and desired output please.

[Anonymous]

Local Product Group Unit	Supplier	Order Value USD BR	Number Suppliers Order
PRAP	TP2048	5,250	1
PRAP	TP2049	85,000	1
PRAP	TP2050	20,746	1
PRAP	TP2051	66	1
PRAP	TP2052	10,000	1
PRAP	TP2053	66,000	1
PRAP	TP2054	9,995	1
PRAP	TP2055	50,000	1
PRAP	TP2056	899	1
PRAP	TP2057	24,020	1
PRAD	TP2058	8,963	1
PRAD	TP2059	8,012,000	1
PRAD	TP2060	1,379	1
PRAD	TP2061	1,200	1
PRAD	TP2062	697,216	1
PRAD	TP2063	3,075,766	1
PRAD	TP2064	1,480	1
PRAD	TP2065	43,820	1
PRAD	TP2066	45,000	1
PRAD	TP2067	10,500	1
PRAD	TP2068	1,453,000	1
PRAD	TP2069	28,737	1
PRAD	TP2070	7,000	1
PRAD	TP2071	82,312	1
PCDV	TP2072	5,070	1
PCDV	TP2073	20,000	1
PCDV	TP2074	840,000	1
PCDV	TP2075	133,980	1
PCDV	TP2076	205,228	1
PCDV	TP2077	1,043,637	1
PCDV	TP2078	554,800	1
PCDV	TP2079	134,353	1
PCDV	TP2080	173,058	1
PCDV	TP2081	2,300	1
PCDV	TP2082	86,837	1
PCDV	TP2083	3,166	1
PCDV	TP2084	1,608,580	1
PCDV	TP2085	396,898	1
PCDV	TP2086	222	1
PCDV	TP2087	1,317,136	1
PCDV	TP2088	20,000	1
PCDV	TP2089	220,000	1
PCDV	TP2090	120,000	1
PRAC	TP2091	172,500	1
PRAC	TP2092	6,480	1
PRAC	TP2093	38,400	1
PRAC	TP2094	15,460	1
PRAC	TP2095	145,360	1
PRAC	TP2096	10,000	1
PRAC	TP2097	32,000	1
PRAC	TP2098	2,500	1
PRAC	TP2099	105,642	1
PRAC	TP2100	31,730	1
PRAC	TP2101	188,850	1
PRAC	TP2102	7,000	1
PRAC	TP2103	4,800	1
PRAC	TP2104	94,875	1
PRAC	TP2105	3,329,746	1
PRAC	TP2106	250,375	1
PRAC	TP2107	32,183	1
PRAC	TP2108	2,474	1
PRAC	TP2109	589	1
PRGP	TP2110	3,071	1
PRGP	TP2111	3,790	1
PRGP	TP2112	223,897	1
PRGP	TP2113	5,930	1
PRGP	TP2114	326	1
PRGP	TP2115	5,725	1
PRGP	TP2116	176,391	1
PRGP	TP2117	12,270	1
PRGP	TP2118	24,938	1
PRGP	TP2119	492	1
PRGP	TP2120	6,447	1
PRGP	TP2121	69,645	1
PRGP	TP2122	16,886	1
PRGP	TP2123	255,336	1
PRGP	TP2124	1,042	1
PRGP	TP2125	8,316	1
PRGP	TP2126	39,112	1
PRGP	TP2127	2,438	1
PRGP	TP2128	1,365	1
PRGP	TP2129	603,008	1
PRGP	TP2130	184,742	1
PRGP	TP2131	30,299	1
PRGP	TP2132	185,794	1

You can use the above table as sample data, for convenience I have kept the column header of this table same as those shown in the attached screenshot(in above post). 

Regarding Desired Output, I need the data representation to be same as that shown in the attached screenshot(in above post).

[smpa01]

@Anonymous 

Basically the logic needs to be like, the Values in Column C are to be summarized by Values in Column A and then the Values in Column B need to be filtered(Only Top 60% Values) by summarized Values of Column C

- Just so I understand,  Order Value USD BR needs to be summarized by Local Product Group Unit 

which is the following

and then the Values in Column B need to be filtered(Only Top 60% Values) by summarized Values of Column C - so does it mean that DAX needs to return the top 60% (Order Value USD BR/sum of Order Value USD BR by Local Product Group Unit)

If I follow the above, for the given dataset, DAX only needs to return this row only as everything else is <=60%

Please confirm

[Anonymous]

Once SUM of Order Value USD BR is done by Distinct Local Product Group Unit, We need to Filter Out All those "Suppliers" from Supplier column which covered Top 60% portion of SUM of Order Value USD BR by Distinct Local Product Group Unit 

[smpa01]

@Anonymous  so what is the desired output for the given dataset?

[Anonymous]

A table as that shown in the attached screenshot should be implemented as Power BI table(or Matrix) visual. Basically, implementing the filter as shown in the attached screenshot and applying it to table(or Matrix) visual.