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 ...
Need Help
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

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

        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