Need a measure = total sum rows a created measure by group

[Kmcdona1]

I have spent days fighting with creating a measure that I need to use in multiple formulas, I have gone through countless post but could not figure this one out.

I have a measure created that is totaling sales based on the filters selected called My Sales Measure.  I need to create a measure that will total up the sales by category to use in various formulas.  I am using this in a Matrix so the user can drill down to City.  Region(highest level) -> State –> City (lowest level)

I need to be able to use this measure in formulas and not just visual.  I am attempting a distribution of sales for each of the categories when looking at it at each level, not on a total basis.  When attempting to build a measure or using "Show as" I receive the following undesired results in red.

The user needs the abilty to change the date filters on the report so the measure needs to be dynamic. 

Any help would be greatly appreciated.  

[johnt75 · Accepted solution]

Try

Level result =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[City] ),
        CALCULATE (
            [My sales measure],
            KEEPFILTERS ( ALLSELECTED ( 'Table'[State] ) ),
            REMOVEFILTERS ( 'Table'[Category] )
        ),
    ISINSCOPE ( 'Table'[State] ),
        CALCULATE (
            [My sales measure],
            KEEPFILTERS ( ALLSELECTED ( 'Table'[Region] ) ),
            REMOVEFILTERS ( 'Table'[Region] )
        ),
    CALCULATE ( [My sales measure], ALLSELECTED () )
)

% measure = DIVIDE( [My sales measure], [Level result])

[Kmcdona1]

@johnt75 Thanks for the super quick response.  I was messing around with it but the Switch does not like my text it throws an error for 'Table'[City]. Here is what the error reads, so I am messing with it now to find a work around.

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[v-shex-msft]

Hi @Kmcdona1,

ISINSCOPE is used to check the current row context level and return the bool value result.

ISINSCOPE – DAX Guide - SQLBI
According to your description, it seems like you modify the formula and return a text value on the parameter place used for true/false values.

Please share some more detail to help us clarify your scenario and test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng