Select one row to not show total (show blank) or eliminate one in hierarchy

[Anonymous]

Hi, 
Have this matrix that I'm trying to build with possibility of drilling down twice. 
One of my row headers has the same name as the next level, which means that it is showing the total twice (see below for "Other")
I'm trying to think if there is a way to remove or hide the total in either one of my first to levels, so that the total will one show once.
Tried with conditional formatting, but could only figure out how to hide both totals, not just one.

Any god suggestions on how to solwe this?

Thank you so much!

[ibarrau · Accepted solution]

Do you mean something like this:

MeasureWithoutTotal3 = 
IF (
    OR(
        ISINSCOPE('Table'[status]), SELECTEDVALUE('Table'[Column1]) <> "Others"
    ),
    SUM(many[value]),
    BLANK()
    )

Hope that's it,

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my formulas.

__Value = 
VAR x = SUM(many[value])
RETURN
IF(
    MAX('Table'[status]) = "Others" && HASONEFILTER('Table'[status]) = FALSE(),
    BLANK(),
    x
)

average = 
VAR x = AVERAGE(many[value])
RETURN
IF(
    MAX('Table'[status]) = "Others" && HASONEFILTER('Table'[status]) = FALSE(),
    BLANK(),
    x
)

count = 
VAR x = COUNT(many[id])
RETURN
IF(
    MAX('Table'[status]) = "Others" && HASONEFILTER('Table'[status]) = FALSE(),
    BLANK(),
    x
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Perfetct! Thanks @v-lionel-msft 

[ibarrau]

Hi. I think you need DAX for this. I can't fiend a way to do it in formatting options. You will need a new Measure for the columns you want to hide and do something like this. Asuming the first column in the drilldown hierarchy's name is "ColumnStatus1"

MeasureWithoutTotal = 
IF (
    HASONEVALUE(Tabla[ColumnStatus1]),
    SUM(Table[Value]), //If value is a column instead of measure you can add it instead of the sum
    BLANK()
)

Hope that helps

[Anonymous]

hI @ibarrau and @amitchandak , thanks!
not 100% what I'm looking for unfortunately. I need to only show blank on the first level of my hierarchy for "Others", but still show value on that level for all the other. This means I need to do some sort of filtering or something to find the "Others" value in my column.

[ibarrau]

Hi. If the only row you don't want to show is Others, change the measure like the following assuming ColumnH1 means the first in hierarchy and ColumnH2 the second.

MeasureWithoutTotal = 
IF (
    OR(
        HASONEVALUE(Table[ColumnH2]) , SELECTEDVALUE(Table[ColumnH1]) <> "Others"
    ), 
    SUM(Table[Value]),
    BLANK()
)

Hope that helps

[Anonymous]

Hi @ibarrau, this is very close to what I'm looking for, but would like to show the total for either the first level or the second. I tried you formula and worked great removing both totals. Then I tried adding another OR function to see if I could include the next level aswell, but couldnt get that to work.
Ideally I would like it to look like this measurewithouttotal second from the right.

I've added the pbix file here in case that's interesting. Thank you so much.

https://www.dropbox.com/s/7qxw78po0kb43zu/add%20column%20to%20one%20table.pbix?dl=0

[ibarrau · Accepted solution]

Do you mean something like this:

MeasureWithoutTotal3 = 
IF (
    OR(
        ISINSCOPE('Table'[status]), SELECTEDVALUE('Table'[Column1]) <> "Others"
    ),
    SUM(many[value]),
    BLANK()
    )

Hope that's it,

[Anonymous]

Thank @ibarrau, this solve my problem! Unfortunately it seems that my total is removed from the measure, but guess I'll have to live with that.

[amitchandak]

@Anonymous ,  Try a mesure like

if(isinscope(Table[column1]) || isinscope(Table[column2]) , [count], blank())

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/