Forum Discussion

DK-C-87's avatar
DK-C-87
Helper I
8 months ago
Solved

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

  • 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-87's avatar
      DK-C-87
      Helper I

      Thank you so much ! 🙂
      It was the last solution with a new measure that did it for me