Calculated measure that won't sum

[Anonymous]

Hi,

I have a question about a calculated measure that won't sum in a table. 

The measure is 

FreightCost Forecast = if(sum(transportorder[freightcost])=0;calculate(transportorder[FreightCost LY]*0,8);blank())

[freightcost] is captured from database (data type is double)

[FreightCost LY] = calculate (sum(transportorder[freightcost]); dateadd (dateforec[date]; -12; MONTH))

I have created a table of the measures

- Each row and sum of column of [FreightCost LY] are correct and works fine. 

- [FreightCost Forecast] shows correct values on each row, but doesn't show sum of column in the table (it doesn't sum individual rows and leaves total empty). 

Data type of [FreightCost Forecast] is Decimal Number according to Power Bi. 

I have tried various alternatives, e.g.  if(sum(transportorder[freightcost])=0;calculate(transportorder[FreightCost LY]*0,8);0) without success. 

Any ideas or instructions to what I'm doing wrong?

Thanks!

Henrik 

[MFelix]

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

[Anonymous]

Hi,

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 

[MFelix]

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

[Anonymous]

Hi @MFelix 

Thank you for reply. Below is my table;

• [freightcost] is captured from database
• [FreightCost LY] = (with editing by your kind response) FreightCost LY = SUMX ( Dateforec; CALCULATE ( SUM ( transportorder[freightcost] ); DATEADD ( dateforec[Date (bins)]; -12; MONTH ) ) )
• [FreightCost Forecast] = if(sum(transportorder[freightcost])=0;calculate(transportorder[FreightCost LY]*0.8);"")
• [Adjusted FreightCost Forecast] = max(sum(transportorder[freightcost]);transportorder[FreightCost LY]*0.8)

Purpose is that [FreightCost Forecast] should be calculated as .8 times last year's freight cost, IF freightcost is missing. 

Henrik

[jdbuchanan71]

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 ()
)

[Anonymous]

Hi @jdbuchanan71

I tried your suggestion, but unfortunately no change.

Henrik

[jdbuchanan71]

Hello @Anonymous Could you share a sample of your model?

[MFelix]

Can you also share a data sample? Need to know what columns you have and the values. No need for actual data just a sample.

Regards,
MFelix

[Anonymous]

Hi @MFelix,

What kind of data do you need? freightcharge is double. Date is date (calendar created in Power BI and linked to Pickupdate in dataset). Date (bins) is Date grouped by months.

I still think that the problem is that Power BI for some reason converts the measure to variant. If I could use other logic to produce the same result or convert it to a summarized measure it would probably solve it.

Henrik

[jdbuchanan71]

Hello @Anonymous 

I put together what I think you are looking for.

From the linked sample file

You can download my sample file here and take a look at the measures:

https://www.dropbox.com/s/eu9rx5206hqpddo/FreightForcast.pbix?dl=0

[AN_PBI]

@@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 ?

[jdbuchanan71]

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
)

[AN_PBI]

Thanks a lot

[Anonymous]

Hi @jdbuchanan71 

Yes, looks very much like my table. Can you get the column FreightCostFroecast to sum total?

Thanks,

Henrik

[jdbuchanan71 · Accepted solution]

Good morning @Anonymous 

I can, yes.

Updated PBIX file available here:  https://www.dropbox.com/s/eu9rx5206hqpddo/FreightForcast.pbix?dl=0

[Anonymous]

@jdbuchanan71 

Thank you very much! Excellent solution. 

Best regards,

Henrik