Forum Discussion

Aazam's avatar
Aazam
Helper I
2 years ago

How to exclude a rows from getting sum?

 

I am creating a Financial Report and got stuck, I have % in my structure and I am following the Template approach to achieve the structure. Everything was working fine but got stuck in the sum of totals. There are some accounts where I assign 0 in my template so they were not getting sum, I used a SUMX approach and this issue was resolved but now I have % values in my structure. I want to exclude them from getting the sum.

Tried NOT CONTAINING but it did not work because it was blanking my row instead of excluding it from the sum.
2nd Approach I tried is to create a new column and create a Flag isPercent true or false and tried to exclude it from sumx but did get succeed.

This is my Dax below.

[Combine-Stack-Total Actual MTD -- Testing Aazam 2 ] = 

SUMX (
FILTER (
SUMMARIZE(
    'GL Department Accounts-Stack',
'GL Department Accounts-Stack'[Accounts Group],
'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID],
'GL Department Accounts-Stack'[GL Department],
'GL Department Accounts-Stack'[IsPercent]
),
NOT CONTAINSSTRING('GL Department Accounts-Stack'[GL Department],"COGS %")
//NOT 'GL Department Accounts-Stack'[IsPercent] = "True"
),
VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]
VAR totalactualMTD = [Total Actual MTD]
VAR Myhomeofficeincome =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41,
ALL ( 'GL Department Accounts-Stack' )
)
VAR MyEXPENSES =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42,
ALL ( 'GL Department Accounts-Stack' )
)
VAR MyHomeOffice = ((MyEXPENSES*-1)+Myhomeofficeincome)
VAR totalrevenue =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 1,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalcogs =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalpayrollexpense =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 10,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totaloperatingexpenses =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 15,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalexternalexpense =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 21,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalrentexpense =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 27,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalotherexpesnsesincome =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 32,
ALL ( 'GL Department Accounts-Stack' )
)
VAR mercsalesGolfOperation =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 104,
ALL ( 'GL Department Accounts-Stack' )
)
VAR mercsalesFBCatering =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 105,
ALL ( 'GL Department Accounts-Stack' )
)
VAR cogsGolfOperation =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 10,
ALL ( 'GL Department Accounts-Stack' )
)
VAR cogsFBcateringBanquet =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 12,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalexternalexpensefinal = ( totalexternalexpense + MyHomeOffice )
VAR grossprofit = ( totalrevenue - totalcogs )
VAR grossprofitpercentage =
DIVIDE ( grossprofit, totalrevenue, 0 )
VAR revpercentage1 =
DIVIDE ( totalpayrollexpense, totalrevenue, 0 )
VAR clublevelEbitdar = ( grossprofit - totalpayrollexpense - totaloperatingexpenses )
VAR revpercentage2 =
DIVIDE ( clublevelEbitdar, totalrevenue, 0 )
VAR corporateEbitdar = ( clublevelEbitdar - totalexternalexpensefinal )
VAR revpercentage3 =
DIVIDE ( corporateEbitdar, totalrevenue, 0 )
VAR corporateEbitda = ( corporateEbitdar - totalrentexpense )
VAR netincome = ( corporateEbitda - totalotherexpesnsesincome )
VAR cogsgolfoperper =
DIVIDE ( cogsGolfOperation, mercsalesGolfOperation, 0 )
VAR cogsfbbanquertper =
DIVIDE ( cogsFBcateringBanquet, mercsalesFBCatering, 0 )
VAR xxx =
SWITCH (
selectvalue,
48, MyHomeOffice,
17, grossprofit,
18, grossprofitpercentage,
29, revpercentage1,
43, clublevelEbitdar,
44, revpercentage2,
51, corporateEbitdar,
52, revpercentage3,
57, corporateEbitda,
74, netincome,
11, cogsgolfoperper,
13, cogsfbbanquertper,
totalactualMTD
) 

RETURN
reeturn

)

 






2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Aazam 

    First create tge following measure with wrong total (not to be used in the report visuals)

    Combine-Stack-Total Actual MTD =
    VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]
    VAR totalactualMTD = [Total Actual MTD]
    VAR Myhomeofficeincome =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR MyEXPENSES =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR MyHomeOffice = ( ( MyEXPENSES * -1 ) + Myhomeofficeincome )
    VAR totalrevenue =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 1,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totalcogs =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totalpayrollexpense =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 10,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totaloperatingexpenses =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 15,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totalexternalexpense =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 21,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totalrentexpense =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 27,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totalotherexpesnsesincome =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 32,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR mercsalesGolfOperation =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40
    && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 104,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR mercsalesFBCatering =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40
    && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 105,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR cogsGolfOperation =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4
    && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 10,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR cogsFBcateringBanquet =
    CALCULATE (
    [Total Actual MTD],
    'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4
    && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 12,
    ALL ( 'GL Department Accounts-Stack' )
    )
    VAR totalexternalexpensefinal = ( totalexternalexpense + MyHomeOffice )
    VAR grossprofit = ( totalrevenue - totalcogs )
    VAR grossprofitpercentage =
    DIVIDE ( grossprofit, totalrevenue, 0 )
    VAR revpercentage1 =
    DIVIDE ( totalpayrollexpense, totalrevenue, 0 )
    VAR clublevelEbitdar = ( grossprofit - totalpayrollexpense - totaloperatingexpenses )
    VAR revpercentage2 =
    DIVIDE ( clublevelEbitdar, totalrevenue, 0 )
    VAR corporateEbitdar = ( clublevelEbitdar - totalexternalexpensefinal )
    VAR revpercentage3 =
    DIVIDE ( corporateEbitdar, totalrevenue, 0 )
    VAR corporateEbitda = ( corporateEbitdar - totalrentexpense )
    VAR netincome = ( corporateEbitda - totalotherexpesnsesincome )
    VAR cogsgolfoperper =
    DIVIDE ( cogsGolfOperation, mercsalesGolfOperation, 0 )
    VAR cogsfbbanquertper =
    DIVIDE ( cogsFBcateringBanquet, mercsalesFBCatering, 0 )
    VAR xxx =
    SWITCH (
    selectvalue,
    48, MyHomeOffice,
    17, grossprofit,
    18, grossprofitpercentage,
    29, revpercentage1,
    43, clublevelEbitdar,
    44, revpercentage2,
    51, corporateEbitdar,
    52, revpercentage3,
    57, corporateEbitda,
    74, netincome,
    11, cogsgolfoperper,
    13, cogsfbbanquertper,
    totalactualMTD
    )
    RETURN
    reeturn

     

    then the flowing measure is to be ised in the report visuals 

    Combine-Stack-Total Actual MTD - Excluding % =
    IF (
    HASONEVALUE ( 'GL Department Accounts-Stack'[GL Department] ),
    [Combine-Stack-Total Actual MTD],
    SUMX (
    FILTER (
    SUMMARIZE (
    'GL Department Accounts-Stack',
    'GL Department Accounts-Stack'[Accounts Group],
    'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID],
    'GL Department Accounts-Stack'[GL Department]
    ),
    NOT CONTAINSSTRING ( 'GL Department Accounts-Stack'[GL Department], "COGS %" )
    ),
    [Combine-Stack-Total Actual MTD]
    )
    )

  • _elbpower's avatar
    _elbpower
    Resolver III
    2 years ago
    VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]
VAR totalactualMTD = [Total Actual MTD]

RETURN
SWITCH (
TRUE (),
'GL Department Accounts-Stack'[IsPercent] = "True", BLANK (), // Exclude rows with percentage values
selectvalue = 48, MyHomeOffice,
selectvalue = 17, grossprofit,
selectvalue = 18, grossprofitpercentage,
selectvalue = 29, revpercentage1,
selectvalue = 43, clublevelEbitdar,
selectvalue = 44, revpercentage2,
selectvalue = 51, corporateEbitdar,
selectvalue = 52, revpercentage3,
selectvalue = 57, corporateEbitda,
selectvalue = 74, netincome,
selectvalue = 11, cogsgolfoperper,
selectvalue = 13, cogsfbbanquertper,
totalactualMTD
)

    In this modified measure, we use the SWITCH(TRUE(), ...) pattern to handle the various cases. If 'IsPercent' is "True" for a row, we use BLANK() to exclude it from the calculation. Otherwise, we proceed with the calculation based on the 'GL_Dept_Group_Sort_ID' value. This way, rows with percentage values will not be included in the sum.

    Make sure to replace the expressions inside the SWITCH function with your actual calculations. This DAX measure should exclude rows with percentages while calculating the desired financial metrics.