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

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 1
Highlighted
Memorable Member
Memorable Member

Re: Alternative Supplier: Calculated Column/Measure Help

Hello @rgold25 ,

 

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

 

 

 

Capture.JPG

 

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.