Forum Discussion

TessSA's avatar
TessSA
Frequent Visitor
1 year ago
Solved

Measure DAX division not working as expected - skipping rows

Hello, I have a table like below (see attached sceenshot):   Opportunity Name SKU Category Total Price % Sales on X A COM          2.823,93 € 2,09% A CONFIG                        ...
Need Help
  • TessSA's avatar
    TessSA
    1 year ago

    Thank you, I had to adapt the DAX a bit but I was able to fix it by using SUMX for my value as follows:

     

    % Sales on X =
    var _category = SELECTEDVALUE('Table'[SKU Category])
    var _value = SUMX('Table', 'Table'[Total Price])
    var _total = [Total X Sales]
    var _result = IF(_category <> "X", DIVIDE(_value, _total, ""), BLANK())
    return _result
     
    My "Total Price" column in my data set is alreayd the Qty * Unit Price. 
    My total variable, [Total X Sales] was already being calculated correctly.
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hello TessSA ,

You can try to use a SUMX nested in a CALCULATE in a VAR for your TOTALX measure, ex:

VAR _category = SELECTEDVALUE('Table'[SKU Category])
VAR _Total Sales = SUMX('Table',
                                          [Price]  *   [Quantity])
VAR _Total X = CALCULATE(
                          SUMX('Table',
                                          [Price]  *   [Quantity]),
                           FILTER('Table',
                                   [SKU Category]  = "X"))

RETURN 
              IF(
                   CALCULATE(
                         MAX('Table'[SKU Category]) 'Table'[SKU Category] = _category)  <> "X",
                         _Total Sales/_Total X, BLANK())
)



 

  • TessSA's avatar
    TessSA
    Frequent Visitor
    1 year ago

    Thank you, I had to adapt the DAX a bit but I was able to fix it by using SUMX for my value as follows:

     

    % Sales on X =
    var _category = SELECTEDVALUE('Table'[SKU Category])
    var _value = SUMX('Table', 'Table'[Total Price])
    var _total = [Total X Sales]
    var _result = IF(_category <> "X", DIVIDE(_value, _total, ""), BLANK())
    return _result
     
    My "Total Price" column in my data set is alreayd the Qty * Unit Price. 
    My total variable, [Total X Sales] was already being calculated correctly.