Forum Discussion

t-dahen's avatar
t-dahen
Microsoft Employee
6 years ago
Solved

Remove All Filters but Specify New Filter

Hello,   Is there a way to remove all filters but specify a new filter inside a dax expression?     For example, what I would think to do is:       Measure = Calculate(SUM(Table[row1]), Tab...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    Try this. I used the logic I gave for the total, then used a slight different logic for the current total used as the numerator.

    Type A Total =
VAR varTypeATotal =
    SUMX(
        FILTER(
            ALL( 'Data Table' ),
            'Data Table'[Type] = "A"
        ),
        'Data Table'[Total]
    )
VAR varCurrentTotal =
    SUMX(
        FILTER(
            'Data Table',
            'Data Table'[Type] = "A"
        ),
        'Data Table'[Total]
    )
VAR varPercentOfTotal =
    DIVIDE(varCurrentTotal, varTypeATotal, 0)
RETURN
    varPercentOfTotal

     

     

     

     

t-dahen's avatar
t-dahen
Microsoft Employee
6 years ago

Perfect, thanks!

Similarily related, is getting a percentage breakdown for a Measure across all the names possible?

 

So for Bob and Alice, let's say an arbitrary measure returns 60 and 40 respectively. Can I get:

 

NamePercentWithMeasure
Bob.60
Alice.40
edhans's avatar
edhans
Community Champion
6 years ago

Yes. that will depend on the filter context of the visual, but table like you have will work fine.

 

Percent by Name =
VAR varGrandTotal =
    SUMX(
        ALL( 'Data Table' ),
        'Data Table'[Total]
    )
VAR varCurrentTotal =
    SUM( 'Data Table'[Total] )
VAR varPercentOfTotal =
    DIVIDE(
        varCurrentTotal,
        varGrandTotal,
        0
    )
RETURN
    varPercentOfTotal