Customize "Other" - sub category formula in a matrix

[aggkamakshi]

Hi Team,

I need help to calculate numbers of distinct members in given below scenario:

Input:

Here we have calculated distinct members in each product and sub product group. This is the output that we have already created by defining various variables.

Expected Output

What we like to have is:

1. Take top 2 sub products within each main product based on count of distinct  member id's

2. All other sub products will be clubbed as "Öthers" and distinct number of members needs to be calculated

Catch here is - same member who have purchased any sub product from top 2 can also purchase any other sub product and hence needs to be calculated in other as well.

What we are doing currently

Right now what we have done is calculated the total number of distinct members and subtracted the number of members who have purchased top 2 sub products - and hence count of member id in others is coming out to be quite low since same customer can be part of both groups. DAX written till now is:

*Referral_Referrals = DistinctCountNoBlank('Table'[Members])

** I have selected 2 from slicer for parameter value

***'Product Name' is a table which we have created consisting of all Sub products and union it with "Others" category

Referrals Referrals =

VAR SalesOfAll = CALCULATE(*[Referral_Referrals],REMOVEFILTERS('Product Name'[Sub Products]))

RETURN

IF(

ISINSCOPE('Product Name'[Sub Products]),

VAR ProductToRank = **[Parameter Value]

VAR IsOtherSelected = SELECTEDVALUE('Product Name'[Sub Products])="Others"

VAR ProductsWithReferralReferrals = ADDCOLUMNS(

ALLSELECTED('Product Name'[Sub Products]),"@Referral",[Referral_Referrals])

VAR Top3Prods = TOPN(ProductToRank,ProductsWithReferralReferrals,[@Referral])

VAR SalesofTop3 = SUMX(Top3Prods,[@Referral])

VAR Result = IF(IsOtherSelected,SalesOfAll-SalesofTop3,[Referral_Referrals])

RETURN

Result,

SalesOfAll

)

Please let us know how we can correct this DAX and can reach to correct calcualions.  

[Anonymous · Accepted solution]

Hi @aggkamakshi ,

So the Top2 sub-type is dynamically changed based on many selections in slicer ? 

If so, your need to use Rank measure instead, like:

=
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Product] = MAX ( 'Table'[Product] )
            && [slicerColumn1]
                IN ALLSELECTED ( 'TABLE'[slicerColumn1] )
                    && [slicerColumn2] IN ALLSELECTED ( 'TABLE'[slicerColumn2] )
    ),
    CALCULATE ( MAX ( ( 'Table'[Distinct Count] ) ) ),
    ,
    DESC
)

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @aggkamakshi ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @aggkamakshi ,

So the Top2 sub-type is dynamically changed based on many selections in slicer ? 

If so, your need to use Rank measure instead, like:

=
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Product] = MAX ( 'Table'[Product] )
            && [slicerColumn1]
                IN ALLSELECTED ( 'TABLE'[slicerColumn1] )
                    && [slicerColumn2] IN ALLSELECTED ( 'TABLE'[slicerColumn2] )
    ),
    CALCULATE ( MAX ( ( 'Table'[Distinct Count] ) ) ),
    ,
    DESC
)

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aggkamakshi]

Intermediate Table
Product Name	Sub Products	Distinct Count (Members)
Fruits	Banana	3
Fruits	Mangos	4
Fruits	Apple	5
Fruits	Orange	6
Fruits	Kiwi	7
Fruits	Pears	1
Vegetables	Spinach	3
Vegetables	Cauliflower	5
Vegetables	Brinjal	6
Vegetables	Potato	8
Vegetables	Onions	3
Furniture	Table	1
Furniture	Chair	3
Flowers	Rose	5
Flowers	Tulip	7
Flowers	Lily	8

Required Table
Product Name	Sub Products	Distinct Count (Members)
Fruits	Kiwi	7
Fruits	Orange	6
Fruits	Others	Distinct Count (Members)
Vegetables	Potato	8
Vegetables	Brinjal	6
Vegetables	Others	Distinct Count (Members)
Furniture	Table	1
Furniture	Chair	3
Flowers	Rose	5
Flowers	Tulip	7
Flowers	Others	Distinct Count (Members)

[negi007]

@aggkamakshi 

in this case first i created rank at prodcut name level like below

Rank_Order = RANKX(FILTER(Intermediate,Intermediate[Product Name]=EARLIER(Intermediate[Product Name])),Intermediate[Distinct Count (Members)])

then based on the rank calc above, we grouped items in top2 and others like below

Product_New = if (Intermediate[Rank_Order]>2, "Others", Intermediate[Sub Products])

below is the output that is asked for 

sharing pbix file as well for your reference. 

[aggkamakshi]

Thank you so much for the solution.

The data which I have given was dummy and in my original data I have many more columns which are being used in slicers. If I am changing slicers, sub-product that are mapped to "others" will change and hence we can't keep it fix.

In the formula shared above, if we can somehow dynamically remove the top 2 sub-products (which chnage according to slicers) from raw data  and then calculate distinct count of members; that will solve the problem. 

[negi007]

@aggkamakshi plese share your data in text format which can be copied.