Forum Discussion
Filter Top N% Column Values by Other Column Values
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 sure no problem. Can you please provide some data and desired output please.
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).
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
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