Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
This code summarises the data correctly and places the values in the Previous Month as required but won't display the correct total.
CALCULATE( SUMX ( SUMMARIZE ( Opportunity, Opportunity[Currency], 'Probability'[Probability Lower Band Value], 'Currency conversion'[Conversion rate NZ to AUD] ), VAR TotalAmount = CALCULATE ( SUM ( Opportunity[Total amount] ) ) VAR ConversionRateFinal = IF ( Opportunity[Currency] = "AUS$", 1, 'Currency conversion'[Conversion rate NZ to AUD] ) VAR ProbabilityFinal = 'Probability'[Probability Lower Band Value]/ 100 RETURN TotalAmount * ProbabilityFinal / ConversionRateFinal / 1000 ),PREVIOUSMONTH('Date'[Date]))
Which produces the correct result for each previous month that align to the current month as displayed in this table below but the total is not correct. I have read about HASONEVALUE but couldn't see how to apply it in this case.
Appreciate the time.
Solved! Go to Solution.
Check if your "Month-Year" column has one value, then do your current calculation.
If not do total calculation.
So something like...
Measure = IF ( HASONEVALUE ( 'Date'[Month-year] ), CALCULATE ( SUMX ( SUMMARIZE ( Opportunity, Opportunity[Currency], 'Probability'[Probability Lower Band Value], 'Currency conversion'[Conversion rate NZ to AUD] ), VAR TotalAmount = CALCULATE ( SUM ( Opportunity[Total amount] ) ) VAR ConversionRateFinal = IF ( Opportunity[Currency] = "AUS$", 1, 'Currency conversion'[Conversion rate NZ to AUD] ) VAR ProbabilityFinal = 'Probability'[Probability Lower Band Value] / 100 RETURN TotalAmount * ProbabilityFinal / ConversionRateFinal / 1000 ), PREVIOUSMONTH ( 'Date'[Date] ) ), "Your total calculation" )
Replace "Your total calculation" with actual measure/DAX formula.
Check if your "Month-Year" column has one value, then do your current calculation.
If not do total calculation.
So something like...
Measure = IF ( HASONEVALUE ( 'Date'[Month-year] ), CALCULATE ( SUMX ( SUMMARIZE ( Opportunity, Opportunity[Currency], 'Probability'[Probability Lower Band Value], 'Currency conversion'[Conversion rate NZ to AUD] ), VAR TotalAmount = CALCULATE ( SUM ( Opportunity[Total amount] ) ) VAR ConversionRateFinal = IF ( Opportunity[Currency] = "AUS$", 1, 'Currency conversion'[Conversion rate NZ to AUD] ) VAR ProbabilityFinal = 'Probability'[Probability Lower Band Value] / 100 RETURN TotalAmount * ProbabilityFinal / ConversionRateFinal / 1000 ), PREVIOUSMONTH ( 'Date'[Date] ) ), "Your total calculation" )
Replace "Your total calculation" with actual measure/DAX formula.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |