Forum Discussion
Wrong total in table
Hi everyone
Hope there is some one out there that can help me 🙂
I have tried myself to correct the total sum in my table (Filtreret benchmark to be total of 8.302.774):
My measure is:
Filtreret benchmark =
VAR _Rul12Kassationer =
CALCULATE(SUM('4_Kas_store'[Kassationer]),
DATESINPERIOD('4_Kas_store'[Fiscal year / period],
MAX('4_Kas_store'[Fiscal year / period]), -12, MONTH),
ALLEXCEPT('3_Stamdata', '3_Stamdata'[Oms cluster])
)
VAR _Rul12AvReg =
CALCULATE(SUM('4_Kas_store'[Avance reguleringer]),
DATESINPERIOD('4_Kas_store'[Fiscal year / period],
MAX('4_Kas_store'[Fiscal year / period]), -12, MONTH),
ALLEXCEPT('3_Stamdata', '3_Stamdata'[Oms cluster])
)
VAR _Rul12Svind =
CALCULATE(SUM('4_Kas_store'[Svind]),
DATESINPERIOD('4_Kas_store'[Fiscal year / period],
MAX('4_Kas_store'[Fiscal year / period]), -12, MONTH),
ALLEXCEPT('3_Stamdata', '3_Stamdata'[Oms cluster])
)
VAR _Rul12KAS = _Rul12Kassationer + _Rul12AvReg + _Rul12Svind
VAR _Count = CALCULATE(DISTINCTCOUNT('3_Stamdata'[Plant - Butik]), ALLEXCEPT('3_Stamdata', '3_Stamdata'[Oms cluster]))
VAR _Benchmark = DIVIDE( _Rul12KAS, _Count )
VAR _Potentiale = _Benchmark - [Rul12 Kas]
VAR _Resultat = IF( [Rul12 Kas] < _Benchmark, _Potentiale, BLANK())
RETURN
SUMX(
VALUES('3_Stamdata'[Oms cluster]),
CALCULATE(_Resultat)
)
The [Rul12 Kas] measure is the same as the VAR _Rul12KAS only without the ALLEXCEPT part
DK-C-87 , Try this return
SUMX(
Summarize('3_Stamdata', '3_Stamdata'[Oms cluster], '3_Stamdata'[Plant - Butik]),
CALCULATE(_Resultat)
)
Or
Return
_Resultat
And then create a new measure=SUMX(
Summarize('3_Stamdata', '3_Stamdata'[Oms cluster], '3_Stamdata'[Plant - Butik]),
[Filtreret benchmark]
)
Also, you should use a date table for all time intelligence functions datesinperiod.
2 Replies
- amitchandakSuper User
DK-C-87 , Try this return
SUMX(
Summarize('3_Stamdata', '3_Stamdata'[Oms cluster], '3_Stamdata'[Plant - Butik]),
CALCULATE(_Resultat)
)
Or
Return
_Resultat
And then create a new measure=SUMX(
Summarize('3_Stamdata', '3_Stamdata'[Oms cluster], '3_Stamdata'[Plant - Butik]),
[Filtreret benchmark]
)
Also, you should use a date table for all time intelligence functions datesinperiod.- DK-C-87Helper I
Thank you so much ! 🙂
It was the last solution with a new measure that did it for me