Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vissvess
Helper V
Helper V

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

Hi,

 

My data set is similar as follows.

SaleIDQtyConfiguration
981081Modualar
759837Segment
875758Pieces
7861102Modular
778384Modular
681830Modular
962584Segment
825722Segment
882289Modular
896212Modular
790483Segment
7218103Pieces
889989Separate

 

If I summarize this as

Row LabelsSum of QtyGrand Total874

ConfigSum of qty
Modualar81
Modular317
Pieces161
Segment226
Separate89

 

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 LabelsSum of Qty(for ref)
Modular31736%
Segment22626%
Pieces16118%
Separate8910%
Modualar819%
Total874100%

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

10 REPLIES 10
Anonymous
Not applicable

Play around with this one:

 

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

 

Best

Darek

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

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

Best
Darek

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 ),
1,
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
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

Best
Darek

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors