Hi, I'm still new to PowerBI and still learning how to write measures.
Currently I'm unable to get my grand totals to work. I have tried using HASONEVALUE but because it is comparing 2 time periods the way I'm writing it doesn't seem to work.
I have the following calculations (where CY = current year, LY = last year) at customer level:
Diff to LY TotalSales = TotalSales CY - TotalSales LY
Value = IF (ISBLANK([TotalSales LY]), BLANK(), (([Price CY] - [Price LY]) * [Units CY Total]))
Volume = [Diff to LY TotalSales] - [Value]
The Volume column is calculating correctly at row level ie customer level however the grand total at the bottom of the column is not correct.
So I then tried to use the HASONEVALUE function but it doesn't work in the case where a customer was present last year but is not present this year. How do I get the grand total to work? This is the measure I tried to write.
Volume by Customer = if (HASONEVALUE(Table 1[ContactGenericName]), [Volume],
SUMX( VALUES(Table 1[ContactGenericName]),[Volume]))
The table looks like this:
Hope this is an easy question for someone. Many thanks.