Forum Discussion

thx1137's avatar
thx1137
Icon for Helper I rankHelper I
2 years ago
Solved

Measure Aggregating to Blank Value

All,

 

Having an issue with a measure aggregating to blank in a table visualization and I'm not sure why.

 

Specifically, I am trying to create a report that shows the current period sales in local currency for each subsidiary, then those values converted to US Dollars based on a rate table.  We are getting conversion rates from this rate table using the LOOKUPVALUE function, where 2 of the 4 values are driven by slicers and the remaining 2 are driven by context in the table visualization.  The table shows the subsidiary, the currency, the sales amount in local currency, the conversion rate and this new measure.

 

The calculations for each subsidary are working as expected and interacting with the slicers appropriately.  The problem is that the new measure isn't totalling...just showing a blank. 

 

I'm thinking this has to do with blanks and how I handle them.  For example, there are subsidiaries where the local sales amount for the current period is blank (not zero but blank).

 

Here's the DAX:

 

FX Adjusted Sales USD = 
VAR PeriodValue = SELECTEDVALUE(Period[Period])  // driven by slicer
VAR Rate_Type = SELECTEDVALUE('Exchange Rate Lookup'[Rate Type]) // driven by slicer
VAR SubValue = SELECTEDVALUE(Subsidiary[Subsidiary])  // driven by context in table
VAR CurrValue = SELECTEDVALUE(Subsidiary[Currency])  // driven by context in table
 
VAR Exchange_Rate = LOOKUPVALUE(
    'Exchange Rate Lookup'[Rate Value],
    'Exchange Rate Lookup'[Period], PeriodValue,
    'Exchange Rate Lookup'[Rate Type], Rate_Type,
    'Exchange Rate Lookup'[Subsidiary], SubValue,
    'Exchange Rate Lookup'[Currency], CurrValue
 
)
 
VAR Sales_Local = [FX Sales - Current Period Local]  // this is a separate measure that shows sales in local for the latest period
RETURN
 
Sales_Local * Exchange_Rate
 
 
Any thoughts?
  • thx1137's avatar
    thx1137
    2 years ago

    Resolved it with a SUMX iterating over the subsidiary table.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Install DAXDebugOutput and then learn how to use EVALUATEANDLOG to debug your code.  You will be able to see exactly where your result turns BLANK() and can correct it as needed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi thx1137 ,

     

    I create simple samples and you can check the results as follows:

    So your measure should also add <alternateResult>

    VAR Exchange_Rate = LOOKUPVALUE(
    'Exchange Rate Lookup'[Rate Value],
    'Exchange Rate Lookup'[Period], PeriodValue,
    'Exchange Rate Lookup'[Rate Type], Rate_Type,
    'Exchange Rate Lookup'[Subsidiary], SubValue,
    'Exchange Rate Lookup'[Currency], CurrValue,
    0
)

     

    LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • thx1137's avatar
      thx1137
      Icon for Helper I rankHelper I
      2 years ago

      Adding 0 as the alternate value for the LOOKUPVALUE statement results in the measure aggregating to 0 across all subsidiaries.  We're instead looking to have the measure aggregate to the sum of sales across all subsidiaries.

  • thx1137's avatar
    thx1137
    Icon for Helper I rankHelper I
    2 years ago

    Resolved it with a SUMX iterating over the subsidiary table.