Forum Discussion

Chalklands's avatar
Chalklands
Helper 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
Community 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
Helper 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

 

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

    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.