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,
I have a question about a calculated measure that won't sum in a table.
The measure is
Solved! Go to Solution.
Good morning @Anonymous
I can, yes.
Updated PBIX file available here: https://www.dropbox.com/s/eu9rx5206hqpddo/FreightForcast.pbix?dl=0
Hi @Anonymous ,
Measures in DAX are calculated based on context so the columns, slicers, filters visuals interactions can change the calculation of a measure.
In your case since the total row doesn't have dateforec[date] associated with it, because it's an aggregation line the result is blank.
You need to redo your measure to have an aggregator for making the sum of the rows. Something similar to:
[FreightCost LY] = SUMX ( Dateforec; CALCULATE ( SUM ( transportorder[freightcost] ); DATEADD ( dateforec[date]; -12; MONTH ) ) )
Be aware that this can change your final result also, without any sample data is difficult to give you a better answer.
If this measure doesn't work please share a sample of your model.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Thank you for your reply, but this didn't solve the problem. My measure [Freightcost Forecast] still doesn't show a column total although I applied the changes to [FreightCost LY] as proposed. Actually, it didn't change the outcome of [FreightCost LY] either. What I want is that I only want [FreightCost Forecast] to show a value only in case [freightcost] is null (as for future dates), which works fine with the if statement in the measure formula. I think that the problem is that the measure gets caulcated as variant and data type variants don't sum in tables. I have tried to use value([Freight Cost Forecast]), but no change.
Henrik
@MFelix wrote:Hi @Anonymous ,
Measures in DAX are calculated based on context so the columns, slicers, filters visuals interactions can change the calculation of a measure.
In your case since the total row doesn't have dateforec[date] associated with it, because it's an aggregation line the result is blank.
You need to redo your measure to have an aggregator for making the sum of the rows. Something similar to:
[FreightCost LY] = SUMX ( Dateforec; CALCULATE ( SUM ( transportorder[freightcost] ); DATEADD ( dateforec[date]; -12; MONTH ) ) )Be aware that this can change your final result also, without any sample data is difficult to give you a better answer.
If this measure doesn't work please share a sample of your model.
Regards,
MFelix
Henrik
Hi @Anonymous ,
As I refer measures and calculations are based in context so the way you setup the tables and the measure will alter the final result.
Can you share a sample model or a mockup with expected result so I can check what has to be changed?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thank you for reply. Below is my table;
I believe the problem is that the missing freight cost is not = 0, it is blank. Try this.
[FreightCost Forecast] =
IF (
ISBLANK ( SUM ( transportorder[freightcost] ) );
[FreightCost LY] * 0,8;
BLANK ()
)
Hello @Anonymous Could you share a sample of your model?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @Anonymous
I put together what I think you are looking for.
You can download my sample file here and take a look at the measures:
https://www.dropbox.com/s/eu9rx5206hqpddo/FreightForcast.pbix?dl=0
@@jdbuchanan71 - I need help with the same issue, but im not able to download the pbix file.
Is it possible to share the measure used for FreightCostLY or FreightCostForecast ?
Sure, here you go.
FreightCostLY =
CALCULATE(
[FreightCost],
SAMEPERIODLASTYEAR(Dates[Date])
)
FreightCostForcast =
VAR FutureMonths = FILTER ( VALUES ( Dates[Date (bins)] ) , [FreightCost] = 0 )
RETURN
SUMX(
FutureMonths,
[FreightCostLY] * .8
)
Thanks a lot
Yes, looks very much like my table. Can you get the column FreightCostFroecast to sum total?
Thanks,
Henrik
Good morning @Anonymous
I can, yes.
Updated PBIX file available here: https://www.dropbox.com/s/eu9rx5206hqpddo/FreightForcast.pbix?dl=0
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |