Alternative Supplier: Calculated Column/Measure Help

[Anonymous]

Hello. I’m trying to create a calculated column to recommend an alternative supplier using Dax. Ideally I would like to select the supplier that has the largest invoice amount within the grouped business unit, Payment Type of Direct, and matching Category in that order. For example, in the sample data set Houston is chosen as the alternative supplier to supplier Raleigh b/c Houston has the largest invoice amount within the business unit: East, Payment Type is Direct, and the category is fabric. Any help on how to create a calculated column or measure to achieve this result would be greatly appreciated. Thank you.

Supplier	Business Unit	Payment Type	Category	Invoice Details	Invoice Amount	Alternative Supplier
Houston	East	Direct	fabric	Spacer	5300
Raleigh	East	Direct	fabric	screws	200	Houston
Chicago	North	Direct	fabric	Plate	5600
Times Square	West	Direct	metal	Screws	4400
Brooklyn	North	Direct	metal	Screws	450	Times Square
Raleigh	South	Direct	service	remove plate	500
Washington D.C	West	Direct	Machine	plate	200
Washington D.C	West	Direct	metal	plate	500	Times Square
Dallas	South	Indirect	fabric	plate	400
Chicago	East	Unassigned	metal	plate	555
Austin	North	Unassigned	fabric	spacer	332

[vivran22]

Hello @Anonymous ,

You may try this:

Alternate = 
VAR _BU = dtTable[Business Unit]
VAR _PT = dtTable[Payment Type]
VAR _CT = dtTable[Category] 

VAR _Filter = 
    FILTER(
        dtTable,
            dtTable[Business Unit] = _BU
                && dtTable[Payment Type] = _PT
                    && dtTable[Category] = _CT
                    
    )
VAR _Max = 
    CALCULATE(
        FIRSTNONBLANK(dtTable[Supplier],MAX(dtTable[Invoice Amount])),
        _Filter
    )
VAR _Check = 
    IF(dtTable[Supplier] = _Max,"",_Max)
RETURN
_Check

I am not sure how you got the Brooklyn alternate supplier as Times Square as it doesn't meet the criterias you have defined.

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Feel free to email me for any BI needs.
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter