Forum Discussion

TaariqHussain's avatar
TaariqHussain
Icon for Helper I rankHelper I
1 year ago
Solved

Filter calculation in a measure

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.
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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.

  • TaariqHussain's avatar
    TaariqHussain
    Icon for Helper I rankHelper I
    1 year ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.