Finding TopN by % value instead of a rank to filter results

[ali1234]

Looking for some help or ideas to address this very tricky problem I am facing. I'm sharing a sample dataset with existing measures. A brief explanation of these follows:

I have 2 fact tables providing information about the following:

IBIC2: giving Prod 1-5 size of installed base (units in the field)

KG_Extract1: giving Component consumption against the Products

These two fact tables then connect to two dimension tables with hierarchy of Products and Regions. My model and dataset can be accessed here: 

https://drive.google.com/file/d/1Rf1cvGx1_JUydXVk7Wyq6TuqBCIAAPS-/view?usp=drivesdk

I am able to calculate Part_IB using a measure that finds the part's consumption on a given product. And then adds the IB size of those products. I need to modify this measure and create a measure that ignores the bottom 30% (or any number) of consumption when evaluating the IB size for a Part. See component 5 that was used by Prod 2 and Prod 1. However, 71% of consumption was on Prod 2. So, for the IB size, I'd like to show the IB of only Prod 2 (that is 17). My current measure shows the size as 36 which is size for Prod 1 + Prod 2. How do i achieve this filteration? 

[MFelix]

Hi @ali1234 ,

Create an what if parameter from 0 to 1 and with increase of 0.01 then format as percent, this will allow you to add the value you want to have has your variation percentage.

Then add the following measure:

part_ib filtered = 
VAR temp_Table =
    SUMMARIZE (
        FILTER (
            tbl_OPH3;
            (
                [Quantity_Tot]
                    / CALCULATE ( [Quantity_Tot]; ALL ( tbl_OPH3[OPH Product_Group] ) ) >Parameter[Parameter Value]
            )
        );
        tbl_OPH3[OPH Product_Group];
        "@PartIB"; [Part_IB]
    )
RETURN
    SUMX ( temp_Table; [@PartIB] )

Check PBIX file attach.

[ali1234]

Thank you so much @MFelix for your detailed response. Very helpful indeed. It seems I don't have the latest version so couldn't open the attached PBIX file you shared.

However, i took your measure and created it in my model. It does work for Component 4 and 5 with the 30% limit. However, I tried to raise the limit to 50% and the results are not as expected. Please see the original images I had attached. With 50% being the limit, no product should make the cut for Component1 and it's IB size should show up as 0. However, I find the 'part_ib filtered' measure giving 15 for Component1. Similarly, for Component 7, only Prod 4 should be counted (hence, Ib size of 15), but I see 32 (no change from original Part_IB measure). 

When I raise the limit to 1, i expect only Components 6, 2 and 3 to have any IB.. but that's not case. For Component 4 and Component 5 the IB values are eliminated but 7 and 1 still show IB values.

Can you confirm this is the same in your version of the model? Any thoughts why filtering isn't working in these cases? 

Thank you again for your help

[MFelix · Accepted solution]

Hi @ali1234 ,

You are correct on your analisys having you explanation also made it clear for me change your measure to the following and believe that the result is correct.

part_ib filtered = 
VAR temp_Table =
    SUMMARIZE (
        FILTER (
            tbl_OPH3;
            (
                [Quantity_Tot]
                    / CALCULATE ( [Quantity_Tot]; ALL( tbl_OPH3 ) ) >= Parameter[Parameter Value]
            )
        );
        tbl_OPH3[OPH Product_Group];
        "@PartIB"; [Part_IB]
    )
RETURN
    SUMX ( temp_Table; [@PartIB] )

If not please get back to me with some additional info that you shows as a misscalculation so I can improve the measure further.

[ali1234]

Great.. thanks a lot @MFelix .

I had tried by adding another All(IBIC2) and that seemed to produce the result as well. This looks more elegant though. I'll try in the morning and confirm. I do need to filter the results on regions and product hierarchies (not included in my sample dataset), so hopefully, the measure will see those filter contexts coming through.

I do have a relaetd question.. i can enter a separate post for that if you would prefer. 

In the same model, how do  I make a measure that concatenates the names of the top 3 products that used a component? Right now, I have the top table showing the entire metric of components and Prod relationship. In my actual report, I'd like to add a column in the matrix view that shows the top 3 products that consumed most of the part (so need TopN but concatenate the names of the Prods).

Thanks again!

[MFelix]

Hi @ali1234 ,

When you refer to the top 3 you want to have the first 3 produts or do you want it also to be based on the selection of the slicer?

So if you have 70% it's only the first one if you change to 50% will give 3 if there are 4 items.

[ali1234]

@MFelix , Yes its based on slicers/selections. If only 1 product has all the quantity consumption then off course the measure will only show 1 product name. I have actually created a new thread for this measure so you can get acknowledgement for resolving multiple issues (this one I have already accepted the reply). The new thread is: https://community.powerbi.com/t5/DAX-Commands-and-Tips/concatenating-top-customer-product-names-in-a...

I do have two related question to this IB size measure. I have uploaded a new file (

https://drive.google.com/file/d/1K5PMiLYH3X5zZBWmb0JikgXEgbptZJZ2/view?usp=drivesdk)

that adds a dimension table which defines groups for components. So, component 3, 5, 7 and 1 are in Group1 and remaining components are in Group2. The measure's not producing the right result for the Group IB_Size_Filtered. The group's IB should be a union of the component's IBs.. As an example, Group1 IB size should be sum of

Prod 5 --> coming from Component 3

Prod 2 --> coming from Component 5

Prod 2 + Prod 4 --> coming from Component 7

Nothing coming from Component1

So, total should be Prod 2 + Prod 4 + Prod 5 = 17 + 15 + 22 = 54.

The measure is giving a blank value. 

The value of 36 being given for Group2 is also not correct. Based on the same logic as above, Group2 IB size should be:

Prod 1 + Prod 4 + Prod 2 = 19 + 15 + 17 = 51.

My matrix will be laid out like the pic shows below so would be wonderful to have 1 measure that handles the Components and Group IB_Sizes together. I can also take 2 separate measures, 1 for Component and 1 for Group's (subtotal row).

The second modification I'd appreciate to have is to remove any time context associated with the IB size and when comparing against the parameter for %. In my actual dataset, consumption is given by month but that context should be ignored when deciding which products to pick for calculating the IB? Actual IB is calculated based on the months but selection of products needs to be done across all given time span (as many parts have very few transactions in a given month so the % calculation won't be accurate for such small slice). I am thinking i could change the [Quantity_Tot] in numberator to

Calculate([Quantity_Tot], All(Dates))

and do the same for numerator by adding All(Dates) in the filter list for Calculate. Wouldn't that work? 

Thank you so much once again..

Following image is for the first modification I explained above.

[MFelix]

Hi @ali1234 ,

Try this measure:

part_ib filtered = 
SUMX(Comp_Groups;SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Filter(
                tbl_OPH3; (
                    [Quantity_Tot] / CALCULATE([Quantity_Tot]; ALL(
                        tbl_OPH3)
                    ) >0,4
                )
            ); tbl_OPH3[OPH Product_Group]
        ); 
        "Prt_IB_Fil"; [Part_IB]
    ); [Prt_IB_Fil]
)
)

this may cause some performance issues depending on the size of the data check the link wiht some solutions regariding SUMX within SUMX

https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/

[ali1234]

Thanks @MFelix  for your continued support. This created performance issues indeed as I have over 50,000 different parts in about 150 different groups. I'll read the link info in detail to see if I can improve upon it. 

Thank you again very much for all your help!