Function that does not ignore filters

[Waseem]

Dear All

I am using following formula to calculate a measure value:

Net Amount = SUM([Amount])-CALCULATE(SUM([Amount]),FILTER(ALL('Subs'),[DESCRIPTION]="Rental"))

However, when i apply filters in the graphs, all numbers get ruined. Is there an alternative to "ALL" that does not ignore filters but gives same results?

Regards 

[Phil_Seamark]

HI @Waseem

Please try this tweak as a quick test

Net Amount = SUM([Amount])-CALCULATE(SUM([Amount]),FILTER(ALLSELECTED('Subs'),[DESCRIPTION]="Rental"))

[Waseem]

Hi Phil

I already tried "ALLSELECTED" as well but same impact.

Appreciate though.

Regards 

[Anonymous]

Allselected not working could be due to your data model. If you can share more info that may help.

Also try removing the argument from the allselected as follows 

Net Amount =
SUM ( [Amount] )
    - CALCULATE (
        SUM ( [Amount] ),
        FILTER ( ALLSELECTED (), [DESCRIPTION] = "Rental" )
    )

[Waseem]

Hi Thomas

I have following model linked from excel:

I am trying to sumup the BILLAMOUNT column by filtering out three items (highlighted in yellow) from column "DESCRIPTION". The results are fine when I do not apply any filters. However, when I apply filters on the "Equip ID" column, which is linked to another amapping file, I get messed up numbers. Appreciate your help.

[v-juanli-msft]

Hi @Waseem

"when I apply filters on the "Equip ID" column, which is linked to another amapping file, I get  messed up numbers. "

what is the another amapping file, is it a table which having a relationship with this table?

What is the relationship between this table and "another amapping file"?

Best Regards

Maggie

[Waseem]

Hi @v-juanli-msft

The structure of data is such that

1-I have a two tables for revenues, one of which contains product and channel ID information against each record while other has monthly accumulated numbers without product and channel code.

2-I allocated second table revenue (the aggregated one) by pulling it in first revenue file (that contains channel and product codes) by allocating revenues against each line item.

3-I then summed up two revenue columns to get total revenue.

Now when I prepare a chart of total revenue with date on X-axis, i get perfect result. However when I prepare same graph with products or channels on x-axis, I get some products/channel information in negative. There is no negative value in my revenue columns.

Thanks a lot for your concerns and would appreciate if you could help me to resolve it.

Cheers