Find vendors having same Top 1 product as selected vendor

[uladzimir_uk]

Hello,
Hope you are doing well.
I have been struggling to accomplish a tricky case. Unfortunately, I haven`t found anything similar in other topics. 
Could you please assist me how to achieve the desired result using DAX expressions?

Case: 

1. Based on the user`s selection of vendor, I need to find TOP 1 Product for this vendor. (done)

CALCULATE(FIRSTNONBLANK(table[category],1),TOPN(1,ALL(table[category]), [SUM AMOUNT]),

VALUES(table[category]))

2. Right after this I need to find all vendors that have the same product as top 1.
3. Top 20 of vendors from the previous step.
4. Based on on the result from previous step, I need to do something like this DIVIDE(SUM(numerator),SUM(denominator))

Please find the sample data below:

vedor_id	category	amount	numerator	denominator
1001	category1	572	2	1
1001	category2	198	2	2
1002	category1	509	2	2
1002	category2	90	2	2
1002	category3	243	2	2
1003	category3	1544	2	2
1004	category1	1621	3	1
1004	category2	390	1	2
1005	category1	9292	2	1
1006	category1	527	2	1

When choosing vendor_id = 1001, top 1 category = "category 1"

Top category for each vendor

vedor_id	category	amount	top_category	numerator	denominator
1001	category1	572	category1	2	1
1001	category2	198		2	2
1002	category1	509	category1	2	2
1002	category2	90		2	2
1002	category3	243		2	2
1003	category3	1544	category3	2	2
1004	category1	1621	category1	3	1
1004	category2	390		1	2
1005	category1	9292	category1	2	1
1006	category1	527	category1	2	1

Top 3 vendors with the top "category 1"

vendor_id	category	amount	top_category	numerator	denominator
1005	category1	9292	category1	2	1
1004	category1	1621	category1	3	1
1006	category1	527	category1	2	1

 Hope all above is clear. If not, please let me know.

[uladzimir_uk · Accepted solution]

Together with my colleagues we were able to find a solution for this:

1. Created a separate table in the database with the list of vendors and their top product by sales;
2. Joined this new table with the fact table in Power BI to get the top product column.

Please find the simplified DAX formula below. It should be adjusted with the changes according to your requirements, but at least it will show the direction to follow.

Assuming that you already have Measure to find Top Product:

KPI Top 20 by Vendor =
VAR __Top1 = [Top Product Sales]
VAR __Vendor =
SELECTEDVALUE ( vendor[vendor_group] )
VAR __1VendorNum =
CALCULATE (
SUM ( fact[numerator] ),
FILTER (
ALL ( fact ),
fact[product_category_code] = __Top1
&& fact[top_product] = __Top1
&& fact[rank_by_top_category] <= 20
),
FILTER ( ALL ( vendor ), vendor[vendor_group] = __Vendor )
)
VAR __OtherVendorsNum =
CALCULATE (
SUM ( fact[numerator] ),
FILTER (
ALL ( fact ),
fact[product_category_code] = __Top1
&& fact[top_product] = __Top1
&& fact[rank_by_top_category] <= 20
),
FILTER ( ALL ( vendor ), vendor[vendor_group] <> __Vendor )
)
VAR __AllVendorsNum = __1VendorNum + __OtherVendorsNum
VAR __1VendorDen =
CALCULATE (
SUM ( fact[denominator] ),
FILTER (
ALL ( fact ),
fact[product_category_code] = __Top1
&& fact[top_product] = __Top1
&& fact[rank_by_top_category] <= 20
),
FILTER ( ALL ( vendor ), vendor[vendor_group] = __Vendor )
)
VAR __OtherVendorsDen =
CALCULATE (
SUM ( fact[denominator]),
FILTER (
ALL ( fact ),
fact[product_category_code] = __Top1
&& fact[top_product] = __Top1
&& fact[rank_by_top_category] <= 20
),
FILTER ( ALL ( vendor ), vendor[vendor_group] <> __Vendor )
)
VAR __AllVendorsDen = __1VendorDen + __OtherVendorsDen
VAR __KPI =
DIVIDE ( __AllVendorsNum, __AllVendorsDen )
RETURN
__KPI

[Greg_Deckler]

@uladzimir_uk You can get all vendors that have the same product using something like

//assuming you have a measure Top1Product based on your first calc
OtherVendors = 
  VAR __Top1 = [Top1Product]
  VAR __Vendor = SELECTEDVALUE('Table'[vendor_id])
  VAR __OtherVendors = FILTER(ALL('Table'),[category]=__Top1 && [vendor_id]<>__Vendor)
...

The __OtherVendors variable has the vendors and rows that you need to complete your calculation.

this:

[uladzimir_uk]

Hello @Greg_Deckler ,

Thank you for your input!
Unfortunately I receive this error 
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Greg_Deckler]

@uladzimir_uk Correct, that VAR has multiple rows in it, all of the vendors. You can use SUMX for example to iterate over the rows to return a scalar value.

[uladzimir_uk]

Hello @Greg_Deckler , 
Thank you for your reply.
I feel like it could be a solution. I will double check it on Monday and will get back to you with the results.

[uladzimir_uk]

Hello @Greg_Deckler ,
Sorry for the late reply.
I had a chance to use the formulas 

OtherVendors1 =

VAR __Top1 = [Top Product]

VAR __Vendor = SELECTEDVALUE(book2[vedor_id])

VAR __Den = CALCULATE(SUM(book2[denominator]),FILTER(ALL(book2),book2[category]=__Top1 && book2[vedor_id]<>__Vendor ))

VAR __Num = CALCULATE(SUM(book2[numerator]),FILTER(ALL(book2),book2[category]=__Top1 && book2[vedor_id]<>__Vendor ))

VAR __Divide = CALCULATE(DIVIDE(__Num,__Den),FILTER(ALL(book2),book2[category]=__Top1 && book2[vedor_id]<>__Vendor ))

return __Divide

I have a concern, that whenever I filter the report by vendor, the KPI is being calculated only by this filtered vendor.
But the idea was to calculate it based on All vendors, having the same product as top1.

please find the no filter\ filterd screenshots attached.