Forum Discussion

Chalklands's avatar
Chalklands
Icon for Helper I rankHelper I
6 years ago

Divide filtered value by unfiltered value

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?

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Chalklands
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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