Distinct Count of Supplier Name based on Concatenated BU and Part

[jovendeluna21]

Hi,

Anyone can help me on this?

I am trying to assigned the sourcing type for the BUPart Concat based on the distinct count of Parent Supplier Name Cleaned.

I have this calculated column however it doesn't give me correct result.

Sourcing Type =

VAR ParentSupplierCount =

        CALCULATE(

            DISTINCTCOUNT(Sheet1[Parent Supplier Name (Cleaned)]),

            ALLEXCEPT(Sheet1,Sheet1[BUPart Concat],Sheet1[MonthYear],Sheet1[Is Inter-Company Supplier])

        )

    RETURN

        IF(

            ParentSupplierCount > 1,

            "Multi-sourced",

            "Single-sourced"

        )

For example, DISC109-162-8654 should be Multi-sourced only but it gives both Single-sourced and multi-sourced.

Then for DISC2122006140 should be Multi-sourced since it has more than 1 distinct Parent Supplier Name.

Then I have a MonthYear and Is Inter-Company Supplier filter that will affect the result. Since the BUPart Concat can be multi-sourced or single-sourced on previous months.

The sourcing type should be a column instead of a measure because I will use the sourcing type as legend for my charts.

Here's the link to the pbi file:

https://drive.google.com/file/d/1PBqyg_QJTmpoDWXqmMpVxR8T6RJywVlk/view?usp=sharing

[jovendeluna21]

The goal is when I visualize that in a chart it will show as multi-sourced only as a whole, considering on the selected multiple months that BUPart Concat have two distinct Parent Supplier Name

[Ahmedx]

everything is in order, the same result should be at the level of the month

you can remove the month if you want:

Sourcing Type = 
VAR ParentSupplierCount =
        CALCULATE(
            DISTINCTCOUNT(Sheet1[Parent Supplier Name (Cleaned)]),
            ALLEXCEPT(Sheet1,Sheet1[BUPart Concat],Sheet1[Is Inter-Company Supplier])
        )
    RETURN
        IF(
            ParentSupplierCount > 1,
            "Multi-sourced",
            "Single-sourced"
        )

[jovendeluna21]

What if regardless of the month, the result should be multi-sourced? How can I do that? 

[Ahmedx]

I wrote to you above, you should write like this

ALLEXCEPT(Sheet1,Sheet1[BUPart Concat],Sheet1[Is Inter-Company Supplier])