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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tonvhemert3
Regular Visitor

Using MIN/MAX function in measure, but the total doesn't add up as expected.

Hi,

 

I am currently facing an issue where my values don't add up as expected. I have a table with different ratio's per country, called Verhoudingsgetallen. Let's say:

 

CountrySolarVerhouding
US0,00194
NZ0,02944
DE0,03948
NL0,04854

 

The measure I use is as following: 

 

DemandSolars =
VAR SolarVerhouding_ =
    MAX ( Verhoudingsgetallen[SolarVerhouding] )
VAR SensorForecast_ =
    SUM ( 'Salesforce budgetForecasts'[Forecast__c] )
RETURN
    SolarVerhouding_ * SensorForecast_
 
I use the MAX function, because I want to have the specific value for the specific country used in my rows. The value I get per country is the correct one, but the total value is incorrect, since it uses the MAX SolarVerhouding number times the Forecast. However, I want the SUM of the individuals rows displayed as total. What can I do?
 
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Try

 

DemandSolars =
SUMX (
    VALUES ( 'Table'[Country] ),
    VAR SolarVerhouding_ =
        CALCULATE(MAX ( Verhoudingsgetallen[SolarVerhouding] ))
    VAR SensorForecast_ =
        CALCULATE(SUM ( 'Salesforce budgetForecasts'[Forecast__c] ))
    RETURN
        SolarVerhouding_ * SensorForecast_
)

 

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

Try

 

DemandSolars =
SUMX (
    VALUES ( 'Table'[Country] ),
    VAR SolarVerhouding_ =
        CALCULATE(MAX ( Verhoudingsgetallen[SolarVerhouding] ))
    VAR SensorForecast_ =
        CALCULATE(SUM ( 'Salesforce budgetForecasts'[Forecast__c] ))
    RETURN
        SolarVerhouding_ * SensorForecast_
)

 

Hi John,

 

Thanks for your quick reply. I've tried this, but now I get a really big number as my total. So I don't think this is the solution.

 

 

I've updated my original post to force context transition, should be OK now I think

Thanks a lot. It works!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.