Forum Discussion

Fragan's avatar
Fragan
Icon for Helper III rankHelper III
6 years ago
Solved

Row Sub Total equals is calculated

Hey,   I have this table :   And i want to calculate foreach day and foreach group 1and2 % wich is (Attribute1/Attribute2)*100 And i want to include `1and2 %` in Group Subtotal and on tota...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    OK, taking a look back at the original post, perhaps something like:

     

    Measure 2 =
VAR __Table =
  ADDCOLUMNS(
    SUMMARIZE(
      'Table 3',
      [Group],
      [Attribute],
      "__Measure",[Measure]
    ),
    "__IncludeInTotals",SEARCH("%",[Attribute],,-1)
  )
RETURN
  IF(
    HASONEVALUE('Table 3'[Attribute]),
    [Measure],
    SUMX(FILTER(__Table,[__IncludeInTotals] = -1),[__Measure])
  )
Fragan's avatar
Fragan
Icon for Helper III rankHelper III
6 years ago

amitchandak 

I dont think i need subtotals, i just want to make Attribute5 the subtotal and not calculate the subtotal again. in other words i want to exclude verything from subtotal and total besides Attribute5

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Going to depend on how you are subtotaling, but:

     

    Measure = 
IF(
HASONEVALUE('Table 3'[Group]),
SUMX (
    DISTINCT ( 'Table 3'[Attributes] ),
    SWITCH (
        'Table 3'[Attributes],
        "Attribute5", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute1" )
            - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute2" )
            - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Attributes] = "Attribute3" ),
            var a = 'Table 3'[Attributes] return
        CALCULATE ( SUM ( 'Table'[Value] ),'Table'[Attributes]=a)
    )
),
"Attribute5"
)