Managing the ALL filter (how to exclude one brand on page?)

[Anonymous]

Hi all,

I have created a overview of turnover, profit and margin % comparing 2 periods.

The periods are: Last 3 months vs. last 3-6 months.

I have then created a formula to measure the margin % impact between the periods for selected fields. This formula will evaluate how much each row within a table has impacted the total margin change between the two periods. This could for example be country table that shows how much each country has impacted the total margin % change between the periods.

QUESTION:

How can i write the formulas, so that I am able to filter out a country, brand or other category for the calculations? My ALL() will remove any filters applied. So i would like to remove any table filters, but apply page filters for example when i calculation my constants of total profit and total turnover. 

Here is the Margin Impact formula, which evaluate both a rows and a total sum of turnover and profit for the full period:

(Delta turnover / Total turnover PERIOD 1 )

*

(delta profit / delta turnover - Profit PERIOD 2 / Turnover PERIOD 2)

Here is the Power BI calcualtions for Margin Impact:

Margin Impact = [Delta Turnover] / [Period 1 turnover constant] * ([Delta Profit] / [Delta Turnover] - [Period 2 profit constant] / [Period 2 turnover constant])

The formulas:

Turnover:

Turnover L3M = CALCULATE(SUM(InitiativeMonitor_table[Turnover (DKK)]),InitiativeMonitor_table[Period select2]="Period1")

Turnover L3-6M = CALCULATE(SUM(InitiativeMonitor_table[Turnover (DKK)]),InitiativeMonitor_table[Period select2]="Period2")

Profit:

Profit L3M = CALCULATE(SUM(InitiativeMonitor_table[Fin. Profit DKK]),InitiativeMonitor_table[Period select2]="Period1")

Profit L3-6M = CALCULATE(SUM(InitiativeMonitor_table[Fin. Profit DKK]),InitiativeMonitor_table[Period select2]="Period2")

Total turnover & profit calculations:

Period 1 turnover constant = CALCULATE([Turnover L3M],ALL())

Period 2 turnover constant = CALCULATE([Turnover L3-6M],ALL())

Period 1 profit constant = CALCULATE([Profit L3M],ALL())

Period 2 profit constant = CALCULATE([Profit L3-6M],ALL())

Delta calculations:

Delta Turnover = [Turnover L3M]-[Turnover L3-6M]

Delta Profit = [Profit L3M]-[Profit L3-6M]

[Anonymous]

Hi @amitchandak  and @v-stephen-msft 

I have uploaded a short video explaining the problem:

https://youtu.be/v9l0l02IMcY 

Hope you can help me 🙂 any help is highly appreciated!

[Anonymous]

Here is link to the file with calculations. 

https://www.dropbox.com/s/v5cf7lkyyd5ch5u/margin%20impact%20calculation.pbix?dl=0

The margin impact calculation measures each fields' impact on the total business margin. Therefore, a big difference between period 1 and 2 within 1 country may have a small impact on the total difference for the entire business because that country was only a small fraction of the entire business.

Currently all the formulas work as-is. But often times some data is flawed and needs to be excluded, so for example if I want to exclude a GPPG category or a country in this example. The Margin impact calculation still evaluates the old values (where everything is included), but this is not correct. It has to subtract the numbers for Denmark or the GPPG from the total calculation, which is displayed in the bottom table.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Yes, the ALL function will remove all filters. If you want to keep the filters, you can use ALLEXCEPT. If you want to keep the slicer filters, you can also use the ALLSELECTED function.

If possible, please provide me with some dummy data and expected results.

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , not very clear. If you just want to remove on filer, you can use removefilters

CALCULATE([Turnover L3M],removefilters (Table[Brand]))

refer

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

Also for rolling 3 and 3 to 6 use time intelligence

example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH))
Rolling 3 before 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAXX('Date',dateadd('Date'[Date],-3,month)),-3,MONTH))