Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
thx1137
Helper I
Helper I

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?
1 ACCEPTED SOLUTION
thx1137
Helper I
Helper I

Resolved it with a SUMX iterating over the subsidiary table.

View solution in original post

4 REPLIES 4
thx1137
Helper I
Helper I

Resolved it with a SUMX iterating over the subsidiary table.

Anonymous
Not applicable

Hi @thx1137 ,

 

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

vtianyichmsft_0-1709012585834.png

vtianyichmsft_1-1709012597929.png

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.

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.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.