Divide filtered value by unfiltered value

[Chalklands]

Hi,

I'm trying to achieve the following in BI:

I have sales split by category: Meat, Fish, Vegetables

I want to calculate a category as a percentage of total sales.

How do I prevent the Total Sales value being filtered when I filter a category using a slicer? eg Meat Sales/Total Sales.

Hope somebody can point me in the right direction?

[v-alq-msft]

Hi, @Chalklands 

Based on your description, I created data to reproduce your scenario.

You can create a measure as follows.

Percentage Of Total Sales = DIVIDE(

    SUM('Table'[Sales]),

    CALCULATE(SUM('Table'[Sales]),ALL('Table'))

)

Result:

If I misunderstand your thought, please show me your sample data and expected output. I am glad to solve the problem for you.

Best Regards,

Allan

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

[Chalklands]

Thank you for your help.

I've replicated your data and it works fine. However in my live report I also have my data potentially filtered by other factors (year, month etc). How would I rewrite the formula to take into account? Is this possible to do?

eg  Meat sales as % of total sales in 2019 or 2018 (depending on which year is selected)?

Thanks in advance!

[v-alq-msft]

Hi, @Chalklands 

Here is my sample data.

Table:

ReportDates:

You can create a measure as follows.

Percentage of Total Sales = 
                            DIVIDE(
                                   SUM('Table'[Sales]),
                                   CALCULATE(
                                       SUM('Table'[Sales]),
                                       ALLSELECTED('Table')
                            )

Result:

Best Regards

Allan

[Chalklands]

Hi Allan,

Thanks for your reply. I think my problem is that I data in different tables eg Dates, Product Details, Sales data. I can't work out how to get the measure to work if different elements are in different tables. ~Here is a screen shot of the data model:

And a link to the pbix file:

category.pbix 

Regards

Pete

[v-alq-msft]

Hi, @Chalklands 

You can change the measure as follows.

Total Sales = 
             CALCULATE(
                 SUM('Sales Data'[Qty invoiced]),
                 ALL('Category Icons'),ALL(vw_PowerBI_ProductList),ALL('Sales Data'),'Dates'
             )

Result:

Best Regards

Allan

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

[Anonymous]

HI

You need to use calculate(sum(x), All(table)) will allow the calculation to happen without filtering the calculation:

https://dax.guide/calculate/

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com