Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Alternative Supplier: Calculated Column/Measure Help

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.

SupplierBusiness UnitPayment TypeCategoryInvoice DetailsInvoice AmountAlternative Supplier
HoustonEastDirectfabricSpacer5300 
RaleighEastDirectfabricscrews200Houston
ChicagoNorthDirectfabricPlate5600 
Times SquareWestDirectmetalScrews4400 
BrooklynNorthDirectmetalScrews450Times Square
RaleighSouthDirectserviceremove plate500 
Washington D.CWestDirectMachineplate200 
Washington D.CWestDirectmetalplate500Times Square
DallasSouthIndirectfabricplate400 
ChicagoEastUnassignedmetalplate555 
AustinNorthUnassignedfabricspacer332 

1 Reply

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    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