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"
)
      • Fragan's avatar
        Fragan
        Icon for Helper III rankHelper III
        6 years ago

        Greg_Deckler 

        Can i do the same for the global column total ?

        Like instead of having "Attribute5" there i'd like to have 1142(which is 239+903)