cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ali1234
Frequent Visitor

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

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? 

 

model_view.jpg

 

1 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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User III
Super User III

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ali1234
Frequent Visitor

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

ali1234
Frequent Visitor

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!

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ali1234
Frequent Visitor

@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.

 

Item_sales.jpg

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/

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ali1234
Frequent Visitor

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors