Forum Discussion

Pradeep_BIA's avatar
Pradeep_BIA
Regular Visitor
1 year ago
Solved

Issue in calculating Percentage share through input values from a virtual table

Hi All,   Please help me with the below DAX :  I have some default values here to see if logic is working or not;  I can see that values are passing correctly from Virtual table to Denominator and...
  • Pradeep_BIA's avatar
    Pradeep_BIA
    1 year ago

    Hi All,
    Thanks a lot for the response. There was a slight change in the requirement, customer count is now calculated based on OEM customer instead of Sold to Customer Code. Now I am able to achive the requirement with the below measure.

    OEM Share % TOP N MPN =
    VAR TopN_Value = SELECTEDVALUE('Top N MPN'[Top N MPN], 5)
    VAR LastXMonths = SELECTEDVALUE('Last X Months'[Last X Months], 3)
    VAR MinCustomerCount = SELECTEDVALUE('Customer Count'[Customer Count], 1)
    VAR SelectedCategory = SELECTEDVALUE('Mapping_Table'[Category])

    VAR DateFilter =
        FILTER(
            ALLSELECTED('Calendar'),
            'Calendar'[Relative Month] >= -LastXMonths &&
            'Calendar'[Relative Month] < 0
        )

    VAR ValidTables =
        CALCULATETABLE(
            VALUES('Mapping_Table'[Table Name]),
            'Mapping_Table'[Category] = SelectedCategory
        )

    // Summary table with correct customer count per MPN
    VAR SummaryTable =
        SUMMARIZE(
            FILTER(
                ALLSELECTED('Fact'),
                'Fact'[TABLE_NAME] IN ValidTables
            ),
            'Fact'[MPN],
            "Sales", CALCULATE(SUM('Fact'[Net Sales USD]), DateFilter),
            "CustomerCount", CALCULATE(DISTINCTCOUNT('Fact'[OEM Customer]), DateFilter)
        )

    // Filter to MPNs meeting min customer count
    VAR FilteredSummary =
        FILTER(SummaryTable, [CustomerCount] >= MinCustomerCount)

    // Top N MPNs by Sales
    VAR TopMPNs =
        TOPN(TopN_Value, FilteredSummary, [Sales], DESC)

    VAR SelectedMPNs =
        SELECTCOLUMNS(TopMPNs, "MPN", [MPN])

    // Extract OEM Customers related to selected MPNs from raw fact table (with filters)
    VAR ValidOEMs =
        CALCULATETABLE(
            VALUES('Fact'[OEM Customer]),
            'Fact'[TABLE_NAME] IN ValidTables,
            'Fact'[MPN] IN SelectedMPNs,
            DateFilter
        )
    VAR NumeratorSales =
        CALCULATE(
            SUM('Fact'[Net Sales USD]),
            FILTER(
                'Fact',
                'Fact'[OEM Customer] IN ValidOEMs && -- Using only verified OEMs
                'Fact'[MPN] IN SelectedMPNs -- Ensuring MPN filtering carries over
            ),
            DateFilter
        )
    VAR DenominatorSales =
        CALCULATE(
            SUM('Fact'[Net Sales USD]),
            'Fact'[TABLE_NAME] IN ValidTables,
            'Fact'[MPN] IN SelectedMPNs,
            DateFilter,REMOVEFILTERS('Fact'[OEM Customer])
        )

    VAR OEM_Share = DIVIDE(
        NumeratorSales,  
        DenominatorSales,  
        0  
    )

    RETURN IF(OEM_Share > 0, OEM_Share, BLANK())
v-achippa's avatar
v-achippa
Icon for Community Support rankCommunity Support
1 year ago

Hi Pradeep_BIA,

 

Thank you for reaching out to Microsoft Fabric Community.

 

Thank you for the response, please use this below measure, only the valid Top MPN and OEM combinations will be displayed and all unrelated rows will be blanked out.

 

TopOEMNumerator =
VAR DateFilter =
    FILTER (
        ALL('Calendar'),
        'Calendar'[Relative Month] >= -3 &&
        'Calendar'[Relative Month] < 0
    )

VAR TopMPNTable =
    SELECTCOLUMNS (
        TOPN (
            2,
            ADDCOLUMNS (
                FILTER (
                    ALL ( Billings_Table[MPN] ),
                    CALCULATE (
                        DISTINCTCOUNTNOBLANK ( Billings_Table[Sold to Customer Code] ),
                        DateFilter
                    ) >= 2
                ),
                "Sales", CALCULATE (
                    SUM ( Billings_Table[Total Sales] ),
                    DateFilter
                )
            ),
            [Sales], DESC
        ),
        "MPN", Billings_Table[MPN]
    )

VAR SelectedOEMs =
    CALCULATETABLE (
        VALUES ( Billings_Table[OEM Customer] ),
        TREATAS ( TopMPNTable, Billings_Table[MPN] ),
        DateFilter
    )

VAR CurrentMPN = SELECTEDVALUE(Billings_Table[MPN])
VAR CurrentOEM = SELECTEDVALUE(Billings_Table[OEM Customer])

VAR IsValidRow =
    CONTAINS(TopMPNTable, [MPN], CurrentMPN) &&
    CONTAINS(SelectedOEMs, [OEM Customer], CurrentOEM)

RETURN
    IF (
        IsValidRow,
        CALCULATE (
            SUM ( Billings_Table[Total Sales] ),
            DateFilter,
            Billings_Table[MPN] = CurrentMPN,
            Billings_Table[OEM Customer] = CurrentOEM
        )
    )

 

Here is the expected output, attached pbix file below for your reference.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thanks and regards,

Anjan Kumar Chippa

 

 

Pradeep_BIA's avatar
Pradeep_BIA
Regular Visitor
1 year ago

Hi All,
Thanks a lot for the response. There was a slight change in the requirement, customer count is now calculated based on OEM customer instead of Sold to Customer Code. Now I am able to achive the requirement with the below measure.

OEM Share % TOP N MPN =
VAR TopN_Value = SELECTEDVALUE('Top N MPN'[Top N MPN], 5)
VAR LastXMonths = SELECTEDVALUE('Last X Months'[Last X Months], 3)
VAR MinCustomerCount = SELECTEDVALUE('Customer Count'[Customer Count], 1)
VAR SelectedCategory = SELECTEDVALUE('Mapping_Table'[Category])

VAR DateFilter =
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Relative Month] >= -LastXMonths &&
        'Calendar'[Relative Month] < 0
    )

VAR ValidTables =
    CALCULATETABLE(
        VALUES('Mapping_Table'[Table Name]),
        'Mapping_Table'[Category] = SelectedCategory
    )

// Summary table with correct customer count per MPN
VAR SummaryTable =
    SUMMARIZE(
        FILTER(
            ALLSELECTED('Fact'),
            'Fact'[TABLE_NAME] IN ValidTables
        ),
        'Fact'[MPN],
        "Sales", CALCULATE(SUM('Fact'[Net Sales USD]), DateFilter),
        "CustomerCount", CALCULATE(DISTINCTCOUNT('Fact'[OEM Customer]), DateFilter)
    )

// Filter to MPNs meeting min customer count
VAR FilteredSummary =
    FILTER(SummaryTable, [CustomerCount] >= MinCustomerCount)

// Top N MPNs by Sales
VAR TopMPNs =
    TOPN(TopN_Value, FilteredSummary, [Sales], DESC)

VAR SelectedMPNs =
    SELECTCOLUMNS(TopMPNs, "MPN", [MPN])

// Extract OEM Customers related to selected MPNs from raw fact table (with filters)
VAR ValidOEMs =
    CALCULATETABLE(
        VALUES('Fact'[OEM Customer]),
        'Fact'[TABLE_NAME] IN ValidTables,
        'Fact'[MPN] IN SelectedMPNs,
        DateFilter
    )
VAR NumeratorSales =
    CALCULATE(
        SUM('Fact'[Net Sales USD]),
        FILTER(
            'Fact',
            'Fact'[OEM Customer] IN ValidOEMs && -- Using only verified OEMs
            'Fact'[MPN] IN SelectedMPNs -- Ensuring MPN filtering carries over
        ),
        DateFilter
    )
VAR DenominatorSales =
    CALCULATE(
        SUM('Fact'[Net Sales USD]),
        'Fact'[TABLE_NAME] IN ValidTables,
        'Fact'[MPN] IN SelectedMPNs,
        DateFilter,REMOVEFILTERS('Fact'[OEM Customer])
    )

VAR OEM_Share = DIVIDE(
    NumeratorSales,  
    DenominatorSales,  
    0  
)

RETURN IF(OEM_Share > 0, OEM_Share, BLANK())