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 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 )
  • 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())

7 Replies

  • 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())
  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Pradeep_BIA
      Regular Visitor
      1 year ago

      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

    • Pradeep_BIA's avatar
      Pradeep_BIA
      Regular Visitor
      1 year ago

      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
  • Nasif_Azam's avatar
    Nasif_Azam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Pradeep_BIA
      Regular Visitor
      1 year ago

      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