Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper V
3 years ago
Solved

SUM a calculated table

Hi,

 

I'm having difficulties summing up the following table:


MyTable  =

FILTER(
    CALCULATETABLE(
        VALUES('Table'[Value (€)]),
        USERELATIONSHIP(DateTable[Date],'Table'[Start Date])),
                ISEMPTY(CALCULATETABLE('Table',
                'Table'[Attribute mod.]="Quote requested",
                USERELATIONSHIP(DateTable[Date],'Table'[Start Date]))))

The end result is a single column table with Value €. However, just wrapping this up in "SUMX(MyTable, SUM('Table'[Value (€)])" will provide an incorrect value, where the filters are not applied. 

I want to sum the values returned by "MyTable" -formula above. Ideas?
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    CarlsBerg999 So this doesn't work?

    MyTable  =

VAR __Table =
FILTER(
    CALCULATETABLE(
        VALUES('Table'[Value (€)]),
        USERELATIONSHIP(DateTable[Date],'Table'[Start Date])),
                ISEMPTY(CALCULATETABLE('Table',
                'Table'[Attribute mod.]="Quote requested",
                USERELATIONSHIP(DateTable[Date],'Table'[Start Date]))))

VAR __Result = SUMX(__Table,[Value (€)])
RETURN
__Result

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    CarlsBerg999 So this doesn't work?

    MyTable  =

VAR __Table =
FILTER(
    CALCULATETABLE(
        VALUES('Table'[Value (€)]),
        USERELATIONSHIP(DateTable[Date],'Table'[Start Date])),
                ISEMPTY(CALCULATETABLE('Table',
                'Table'[Attribute mod.]="Quote requested",
                USERELATIONSHIP(DateTable[Date],'Table'[Start Date]))))

VAR __Result = SUMX(__Table,[Value (€)])
RETURN
__Result
    • CarlsBerg999's avatar
      CarlsBerg999
      Helper V
      3 years ago

      Unfortunately this only works in a visual where there are ID's for each row. The summarizing of these does not add up correctly. I'm not quite sure if report level filters are causing this. 


      Edit:
      --> Answer to this was yes. Report level filters were the root cause. VALUES() -part of the formula was returning an incorrect value for the filter context. Thanks for the help!