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

[Pradeep_BIA]

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 it is calculated for only

selected MPNs ; I have an issue in the Numerator ; It is not working as expected and it is giving values for

all the OEM Customers that causing descrepancies. Can you guys please help.

OEM Share % (Virtual Fix) =

VAR TopNValue = 5

VAR LastXMonths = 3

VAR MinCustomerCount = 3

-- Date Filter

VAR DateFilter =

    FILTER (

        ALL('Calendar'),

        'Calendar'[Relative Month] >= -LastXMonths &&

        'Calendar'[Relative Month] < 0

    )

-- Top MPNs Table

VAR TopMPNTable =

    SELECTCOLUMNS (

        TOPN (

            TopNValue,

            ADDCOLUMNS (

                FILTER (

                    VALUES ( Billings_Table[MPN] ),

                    CALCULATE (

                        DISTINCTCOUNTNOBLANK ( Billings_Table[Sold to Customer Code] ),

                        DateFilter

                    ) >= MinCustomerCount

                ),

                "Sales", CALCULATE (

                    SUM ( Billings_Table[Total Sales] ),

                    DateFilter

                )

            ),

            [Sales], DESC

        ),

        "MPN", Billings_Table[MPN]

    )

-- Denominator

VAR Denominator =

    CALCULATE (

        SUM ( Billings_Table[Total Sales] ),

        TREATAS ( TopMPNTable, Billings_Table[MPN] ),

        DateFilter,

        REMOVEFILTERS ( Billings_Table[OEM Customer] )

    )

-- Filter only OEMs linked to selected Top MPNs

VAR FilteredOEMs =

    CALCULATETABLE (

        VALUES ( Billings_Table[OEM Customer] ),

        TREATAS ( TopMPNTable, Billings_Table[MPN] ),

        DateFilter

    )

-- Numerator

VAR Numerator =

    CALCULATE (

        SUM ( Billings_Table[Total Sales] ),

        TREATAS ( TopMPNTable, Billings_Table[MPN] ),

        DateFilter,

        KEEPFILTERS ( FilteredOEMs )

    )

RETURN

    DIVIDE ( Numerator, Denominator )

[v-achippa]

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

[Nasif_Azam]

Hey @Pradeep_BIA ,

The issue with your DAX measure lies in the numerator logic. You're expecting to restrict it to only the OEM customers related to the Top MPNs, but currently, it's pulling sales for those MPNs across all OEM customers, not just the filtered ones. The FilteredOEMs variable produces a table of distinct OEM Customers. However, when used in the CALCULATE function with KEEPFILTERS, it doesn't correctly filter the OEM Customer column in Billings_Table, because the context transition is missing.

Fix: Apply the OEM Customer Filter Properly

You should apply a filter over the actual column in the base table instead of just using VALUES(...).

Update your Numerator calculation as follows:

-- Updated Numerator
VAR Numerator =
    CALCULATE (
        SUM ( Billings_Table[Total Sales] ),
        TREATAS ( TopMPNTable, Billings_Table[MPN] ),
        DateFilter,
        Billings_Table[OEM Customer] IN FilteredOEMs
    )

This ensures the OEM Customer filter is applied on the base table, thereby restricting the calculation to only those OEM customers that were part of FilteredOEMs.

Another clean approach is to use TREATAS for OEM Customer too:

VAR Numerator =
    CALCULATE (
        SUM ( Billings_Table[Total Sales] ),
        TREATAS ( TopMPNTable, Billings_Table[MPN] ),
        TREATAS ( FilteredOEMs, Billings_Table[OEM Customer] ),
        DateFilter
    )

This way, both MPN and OEM Customer are filtered directly and precisely from their respective virtual tables.

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Pradeep_BIA]

Hi, THanks a lot for you r quick response. I tried in the below way to retun only Numerator and see if it is restrcited to selected MPNs or OEM Customers; when checked in measure, it is showing right total sales, but when dragged with MPN ; TOP 5 MPNs are showing with right values but remaining MPNs are also showing up in the table with values; My aim is to show only selected MPNs and Numerator; When dragged with OEM Customer wrong values are coming and all OEM customers are still showing up; I tried all the suggestions mentioened in these replies. Can you please suggest if anything else is causing issues. 

OEM Share % Numerator (Debug) =
VAR TopNValue = 5
VAR LastXMonths = 3
VAR MinCustomerCount = 1

-- Step 1: Date Filter
VAR DateFilter =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Relative Month] >= -LastXMonths &&
'Calendar'[Relative Month] < 0
)

-- Step 2: Top N MPN Table
VAR TopMPNTable =
SELECTCOLUMNS (
TOPN (
TopNValue,
ADDCOLUMNS (
FILTER (
VALUES ( Billings_Table[MPN] ),
CALCULATE (
DISTINCTCOUNTNOBLANK ( Billings_Table[Sold to Customer Code] ),
DateFilter
) >= MinCustomerCount
),
"Sales", CALCULATE (
SUM ( Billings_Table[Total Sales] ),
DateFilter
)
),
[Sales], DESC
),
"MPN", Billings_Table[MPN]
)

-- Step 3: Filtered OEMs based on Top MPNs
VAR FilteredOEMs =
SELECTCOLUMNS (
CALCULATETABLE (
VALUES ( Billings_Table[OEM Customer] ),
TREATAS ( TopMPNTable, Billings_Table[MPN] ),
DateFilter
),
"OEM Customer", Billings_Table[OEM Customer]
)

-- Step 4: Numerator (Sales for selected OEMs & MPNs)
VAR Numerator =
CALCULATE (
SUM ( Billings_Table[Total Sales] ),
TREATAS ( TopMPNTable, Billings_Table[MPN] ),
DateFilter,
TREATAS ( FilteredOEMs, Billings_Table[OEM Customer] )
)

RETURN
Numerator

[DataNinja777]

Hi @Pradeep_BIA ,

The issue with your numerator calculation comes from how the filter on OEM Customers is being applied. The KEEPFILTERS function does not work as expected when used with a virtual table like FilteredOEMs that isn't directly tied to a column reference. Because of this, your measure ends up including all OEM Customers rather than only those linked to the Top MPNs. To fix this, you should apply the filter using TREATAS, which properly maps the values in FilteredOEMs to the OEM Customer column in your fact table. Here’s the corrected version of the Numerator variable:

VAR Numerator =
    CALCULATE (
        SUM ( Billings_Table[Total Sales] ),
        TREATAS ( TopMPNTable, Billings_Table[MPN] ),
        DateFilter,
        TREATAS ( FilteredOEMs, Billings_Table[OEM Customer] )
    )

This way, both the MPN and OEM Customer filters are correctly applied in context, ensuring that only sales for the OEMs linked to the selected Top MPNs are included in the numerator. The rest of your measure can remain unchanged. This adjustment should resolve the discrepancy you observed.

Best regards,

[Pradeep_BIA]

THrough this measure, I am successfully able to pass only selected MPNs and sales with Intersect step in berween,

Test_Selected_MPNs_Sales =
VAR TopMPNs =
SELECTCOLUMNS(
TOPN(
5, -- Selecting Top 5 MPNs dynamically
ADDCOLUMNS(
FILTER(
ALL(Billings_Table[MPN]),
CALCULATE(DISTINCTCOUNTNOBLANK(Billings_Table[Sold to Customer Code]),
FILTER(ALL('Calendar'), 'Calendar'[Relative Month] >= -3 && 'Calendar'[Relative Month] < 0)) >= 1
),
"Sales", CALCULATE(
SUM(Billings_Table[Total Sales]),
FILTER(ALL('Calendar'), 'Calendar'[Relative Month] >= -3 && 'Calendar'[Relative Month] < 0)
)
),
[Sales], DESC
),
"MPN", Billings_Table[MPN]
)

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

VAR FilteredMPNs =
INTERSECT(VALUES(Billings_Table[MPN]), TopMPNs) -- Ensuring correct row-level filtering

VAR TotalSalesSelectedMPNs =
CALCULATE(
SUM(Billings_Table[Total Sales]),
FilteredMPNs, -- Uses intersection for precise row-level filtering
DateFilter
)

RETURN TotalSalesSelectedMPNs

But My real prblem still exists, I am not able see only relevant OEMs and Sales; Based on above measure I created below one and able to see right OEMs passed after step 5 through debug measure, but last step is not working as expected. I also tried treatas in the final step.

Test_Selected_OEM_Sales =

-- Step 1: Define Date Filter First

VAR DateFilter =

    FILTER(

        ALL('Calendar'),

        'Calendar'[Relative Month] >= -3 &&

        'Calendar'[Relative Month] < 0

    )

-- Step 2: Capture Top N MPNs

VAR TopMPNs =

    SELECTCOLUMNS(

        TOPN(

            5,

            ADDCOLUMNS(

                FILTER(

                    ALL(Billings_Table[MPN]),

                    CALCULATE(DISTINCTCOUNTNOBLANK(Billings_Table[Sold to Customer Code]), DateFilter) >= 1

                ),

                "Sales", CALCULATE(SUM(Billings_Table[Total Sales]), DateFilter)

            ),

            [Sales], DESC

        ),

        "MPN", Billings_Table[MPN]

    )

-- Step 3: Apply INTERSECT for MPN Filtering Before Passing to OEM Selection

VAR FilteredMPNs =

    INTERSECT(VALUES(Billings_Table[MPN]), TopMPNs) -- Ensuring row-level filtering for MPNs

-- Step 4: Capture OEM Customers Based on Filtered MPNs (Selection Step)

VAR SelectedOEMs =

    CALCULATETABLE(

        VALUES(Billings_Table[OEM Customer]),

        Billings_Table[MPN] IN FilteredMPNs, -- Ensuring OEMs are restricted by MPN selection

        DateFilter

    )

-- Step 5: Apply INTERSECT on Selected OEMs (Final Refinement Step)

VAR FilteredOEMs =

    INTERSECT(VALUES(Billings_Table[OEM Customer]), SelectedOEMs) -- Ensuring correct row-level filtering

-- Step 6: Calculate Total Sales Only for the Selected OEMs (Fixed Syntax)

VAR TotalSalesSelectedOEMs =

    CALCULATE(

        SUM(Billings_Table[Total Sales]),

        FILTER(

            Billings_Table,

            Billings_Table[OEM Customer] IN FilteredOEMs -- Correct OEM filtering

        ),

        DateFilter

    )

RETURN TotalSalesSelectedOEMs

[Pradeep_BIA]

Hi, THanks a lot for you r quick response. I tried in the below way to retun only Numerator and see if it is restrcited to selected MPNs or OEM Customers; when checked in measure, it is showing right total sales, but when dragged with MPN ; TOP 5 MPNs are showing with right values but remaining MPNs are also showing up in the table with values; My aim is to show only selected MPNs and Numerator; When dragged with OEM Customer wrong values are coming and all OEM customers are still showing up; I tried all the suggestions mentioened in these replies. Can you please suggest if anything else is causing issues. 

OEM Share % Numerator (Debug) =
VAR TopNValue = 5
VAR LastXMonths = 3
VAR MinCustomerCount = 1

-- Step 1: Date Filter
VAR DateFilter =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Relative Month] >= -LastXMonths &&
'Calendar'[Relative Month] < 0
)

-- Step 2: Top N MPN Table
VAR TopMPNTable =
SELECTCOLUMNS (
TOPN (
TopNValue,
ADDCOLUMNS (
FILTER (
VALUES ( Billings_Table[MPN] ),
CALCULATE (
DISTINCTCOUNTNOBLANK ( Billings_Table[Sold to Customer Code] ),
DateFilter
) >= MinCustomerCount
),
"Sales", CALCULATE (
SUM ( Billings_Table[Total Sales] ),
DateFilter
)
),
[Sales], DESC
),
"MPN", Billings_Table[MPN]
)

-- Step 3: Filtered OEMs based on Top MPNs
VAR FilteredOEMs =
SELECTCOLUMNS (
CALCULATETABLE (
VALUES ( Billings_Table[OEM Customer] ),
TREATAS ( TopMPNTable, Billings_Table[MPN] ),
DateFilter
),
"OEM Customer", Billings_Table[OEM Customer]
)

-- Step 4: Numerator (Sales for selected OEMs & MPNs)
VAR Numerator =
CALCULATE (
SUM ( Billings_Table[Total Sales] ),
TREATAS ( TopMPNTable, Billings_Table[MPN] ),
DateFilter,
TREATAS ( FilteredOEMs, Billings_Table[OEM Customer] )
)

RETURN
Numerator