topic Re: Filter a column based on top N aggregation value of another column in DAX Commands and Tips

Filter a column based on top N aggregation value of another column

vissvess — Wed, 31 Jul 2019 16:02:20 GMT

Hi,

My data set is similar as follows.

SaleID	Qty	Configuration
9810	81	Modualar
7598	37	Segment
8757	58	Pieces
7861	102	Modular
7783	84	Modular
6818	30	Modular
9625	84	Segment
8257	22	Segment
8822	89	Modular
8962	12	Modular
7904	83	Segment
7218	103	Pieces
8899	89	Separate

If I summarize this as

Row LabelsSum of QtyGrand Total874

Config	Sum of qty
Modualar	81
Modular	317
Pieces	161
Segment	226
Separate	89

I need a filter or slicer of custom discrete percentage value that should select the topN config that contribute to say, 80% of the total.

Else a slider for the percentage value say, 80% to filter the config column based on sum of topN corresponding qty which accounts for the selected percentage of volume.

Two card (say buttons) to be shown stating the above and below discrete aggregate available for selection, that for the selected slicer/filter/slider value.

Lets elaborate,

Row Labels	Sum of Qty	(for ref)
Modular	317	36%
Segment	226	26%
Pieces	161	18%
Separate	89	10%
Modualar	81	9%
Total	874	100%

So, if a filter/slicer/slider is selected for say 80%,

one card/button to show the "lesser aggregate value is 62%" (is the sum of modular & segment)

another card/button to show the "higher aggregate value is 81%" (is the sum of modular, segment & pieces)

If any one of the two card is selected, the filter has to be applied accordingly.

I don't think the requirement is impractical with so many enthusiasts out here...

Advance thanks for the solution.

Re: Filter a column based on top N aggregation value of another column

Anonymous — Wed, 31 Jul 2019 23:11:35 GMT

Play around with this one:

https://1drv.ms/u/s!ApyQEauTSLtO6m_fBeCmFvvD5AKO

Best

Darek

Re: Filter a column based on top N aggregation value of another column

vissvess — Thu, 01 Aug 2019 00:30:13 GMT

Dear Anonymous ,

So far, so good.

The final step is to select either lesser/higher aggregate, which in turn filters/multiple selects the configuration column.

Kindly provide workaround for the same.

Thanks in advance

vissvess

Re: Filter a column based on top N aggregation value of another column

vissvess — Wed, 07 Aug 2019 14:11:52 GMT

Hi Anonymous ,

The solution is awesome...

Lets change a slight requirement.

Everything your worked around is fine and very handy.. Thanks a lot...

Could you please suggest me how to use the percent filter in the top to filter the table below to its cumulative percentage.

If say 80% is selected, if lesser aggregate is closer than higher aggreate to the selected slicer value, lesser aggregate value filter should be applied.

Similarly if higher aggregate is clocer to the selected slicer value than the lesser aggregate, the vice versa to be happen.

Pl. do the needful.

Thanks in advance

Re: Filter a column based on top N aggregation value of another column

Anonymous — Thu, 08 Aug 2019 08:33:18 GMT

It's ,of course, doable but I have to find some time to look at this...

Best
Darek

Re: Filter a column based on top N aggregation value of another column

vissvess — Fri, 09 Aug 2019 09:01:00 GMT

Hi Anonymous ,

In the attached file from you, you have a measure created by you for cumulative % of total.

I used the same measure for my visual and other calculation.

When I used the measure in a filtered table, the measure gets recalculates for the visible range summation which I do not need.

Kindly suggest a way for me to get the cumulative percentage displayed in the right sense.

I need only the filters applied on page and report level (Date, tray size & unit status) to affect and the filter applied on the visual level not to affect the result.

Kindly do the needful.

Additionally, If I use "switch" over the measure you give, I may use the same as indicator say till 80% show as First, next 15% shows as second and remaining 5% shows as last. Is this sounds correct way.

Thanks

Re: Filter a column based on top N aggregation value of another column

vissvess — Sat, 10 Aug 2019 12:49:09 GMT

Hi Anonymous ,

Based on your work, I have modified the formula to suit my need as follows.

Cumulative % of Total =

var __visibleConfigs = ALL(ConfigDetails[Chassis])

var __totalQtyForVisibleConfigs =

CALCULATE(

[Quantity],

__visibleConfigs

)

var __currentQty = [Quantity]

var __visibleConfigsWithGreaterOrEqualQty =

FILTER(

__visibleConfigs,

[Quantity] >= __currentQty

)

var __cumulativeQty =

CALCULATE(

[Quantity],

__visibleConfigsWithGreaterOrEqualQty

)

var __cumulativeQtyPercent =

if (

ISEMPTY( __visibleConfigsWithGreaterOrEqualQty ),

DIVIDE( __cumulativeQty, __totalQtyForVisibleConfigs )

)

return

__cumulativeQtyPercent

This is great, but on applying a filter, the total gets recalculated to the filtered contents summing to 100%.

But also I need the filter to be only retained for one filter in the same column and two other filters in two other different columns from two tables.

Please provide the range selection for _visible configs.

I have started reading many articles in sqlbi.com to understand the nature of ALL, ALLEXCEPT & more.

Yet, need your kind help.

Thanks

Re: Filter a column based on top N aggregation value of another column

Anonymous — Mon, 12 Aug 2019 09:14:54 GMT

Mate, I've added one more measures [Closest Cummulative Value]. Have a look, it's in the same file.

What do you mean by gets recalculated for the visible range summation? I thought you wanted to calculate the cummul percentage for the configs visible in the visual. This makes sense. If you want to calculate this for all the configs in your table, you'll have to change ALLSELECTED in the measures to ALL.

Best
Darek

Re: Filter a column based on top N aggregation value of another column

Anonymous — Mon, 12 Aug 2019 09:16:23 GMT

Without knowing the full model and the exact requirements (you mention some columns but without names and where they belong), I can't give you any solution.

Best
Darek

Re: Filter a column based on top N aggregation value of another column

vissvess — Mon, 12 Aug 2019 10:17:02 GMT

Yes Anonymous ,

You are right. I donot want it to get recalculated on the visible range.

I have converted the same as mentioned by you.

But there is a small addition in the requiremnt. It has to consider the only three filters and other than that to be removed.

The three filters are calender[Date], 'time bucket'[bucket] & Config[Traysize].

So, Kindly help for this first requirement.

Also, another requiremtn is a seperate measure using the above measure to display a text as such F(<=80%), M(>80% & <=95%) and R(>95%).

Please help me with the two above requriemnt.

Thanks

Re: Filter a column based on top N aggregation value of another column

Anonymous — Mon, 12 Aug 2019 10:32:21 GMT

I need to know the model's layout to do that.

Best
Darek