Filter calculation in a measure

[TaariqHussain]

Good day All

I need assistance in calculating % of sales, example below

I have sales for 5 Items, however only 3 are on promotion, I am using the filter by list (Devscope) to filter my sales to show only the promo sales in my table, however i want to create a card that shows the % of promo sales vs total sales so simply below

totalsales % difference from totalsales =

VAR __BASELINE_VALUE = SUM('CapeTown_itemwisesales'[totalsales])

VAR __VALUE_TO_COMPARE = SUM('CapeTown_itemwisesales'[totalsales])

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

However In need the SUM('CapeTown_itemwisesales'[totalsales])  In the baseline value to be filtered by the Promo sales

If there is another way aroudn filtering the Promo sales that would make this work as well will be good, in my actual data its not only 5 items its quite a bit so i just paste the item codes in the filter by list visual.

[Anonymous · Accepted solution]

Hi @TaariqHussain 

Thank you very much bhanu_gautam for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

“CapeTown_itemwisesales”

“PromoList”

Add a calculated column in the CapeTown_itemwisesales table to indicate whether an item is on promotion:

IsPromo = IF(RELATED('PromoList'[ItemCode]) <> BLANK(), 1, 0)

Create a measure to calculate the total promo sales:

PromoSales = CALCULATE(SUM('CapeTown_itemwisesales'[TotalSales]), 'CapeTown_itemwisesales'[IsPromo] = 1)

Create a measure to calculate the total sales:

Measure TotalSales = SUM('CapeTown_itemwisesales'[TotalSales])

Create a measure to calculate the percentage of promo sales versus total sales:

PromoSalesPercentage = DIVIDE([PromoSales], [Measure TotalSales], 0)

Here is the result.

Regards,

Nono Chen

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

[TaariqHussain]

I dont have it in the same sales report, its a completelt different list in which i only have the item codes that are participating in the promo, no sales values, is there a way for me to link the sales data to the promo list so that it can maybe add a column in the sales data for Promos?

[bhanu_gautam]

@TaariqHussain , First  create a measure for

TotalSales = SUM('CapeTown_itemwisesales'[totalsales])

Assuming you have a column that identifies whether an item is on promotion (e.g., 'CapeTown_itemwisesales'[IsPromo]), you can filter the sales to only include promo items.

PromoSales = CALCULATE(
SUM('CapeTown_itemwisesales'[totalsales]),
'CapeTown_itemwisesales'[IsPromo] = TRUE
)

Then  PromoSalesPercentage = DIVIDE([PromoSales], [TotalSales], 0)

Add a card visual to your report.
Set the value of the card to the PromoSalesPercentage measure.