Forum Discussion

fazou's avatar
fazou
Frequent Visitor
1 year ago
Solved

Filter measures with two same filters

Hello Guys,

 

AS you can see in the screenshot i have a bar chart with two different measures SALES_COMPARISON and SALES_REFERENCE
SALES_REFERENCE will be filtered by two filters of REFERENCE_SEASON  and week of Yar as Axe X

SALES_COMPARISON  will be filtered by two filters COMPARISON_SEASON


two filters section have same columns :
PRODUCT_SEASON from SALES table
WEEK of Year from DATE_DIMENSION

what i want to do is to choose values from REFERENCE SEASON and compare them to values from filter section COMPARISON SEASON as shown below,but it didn't work


 any one have an ida how to achieve it please ?
do i need to duplicate SALES and DATE tables if it is the soliution would be so much to duplicate a sales table ?

thank you for your help 🙂 

 

https://drive.google.com/file/d/1VNvq-6D-9T9ft-OF1aU58Qm9LNpNA27A/view?usp=sharing 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi fazou ,

     

    You can create another date table as a slicer, don't create a relationship with the other table, and create a measure

    and cancel the interaction with other slicers.

    Measure = IF(MAX('DATE_DIMENSION'[Week of Year])<=SELECTEDVALUE('Date2'[WeekOfYear]),
CALCULATE(SUM('SALES'[NET_SALES]),FILTER(ALLSELECTED('DATE_DIMENSION'),'DATE_DIMENSION'[Week of Year]<=MAX('DATE_DIMENSION'[Week of Year]))))

    Result:

     

    Best Regards,
    Zhu

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi fazou ,

     

    1. Please create two more tables, the same SALES and DATE_DIMENSION tables, replacing the fields and slicers used for one of the measures with the fields in the new tables.

     

    2. Modify the original formula without ignoring other filters:

    SALES_REFERENCE = SUM(SALES[NET_SALES])
SALES_COMPARISON = SUM(NEW_SALES[NET_SALES])

     

    3. Create a new date table, and the relationship is as follows:

     

    4.Use the field in the new date table as the x-axis. Result:

    Best Regards,
    Zhu

     

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

    • fazou's avatar
      fazou
      Frequent Visitor
      1 year ago

      Anonymous  Any idea on how to calculate the runing total ? for exp if you select week 44 it SHOW NETSALES OF WEEK 44 and all previous weeks in line chart ? thank you

      i tried this 

      SALES_REFERENCE = CALCULATE(SUM('SALES'[NET_SALES]),FILTER(ALLSELECTED('DIM_DATE'),DIM_DATE[WEEKOFYEAR]<=MAX(DIM_DATE[WEEKOFYEAR])),DIM_DATE[WeekOfYear]=SELECTEDVALUE(DIM_DATE[WeekOfYear])


      )
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi fazou ,

         

        You can create another date table as a slicer, don't create a relationship with the other table, and create a measure

        and cancel the interaction with other slicers.

        Measure = IF(MAX('DATE_DIMENSION'[Week of Year])<=SELECTEDVALUE('Date2'[WeekOfYear]),
CALCULATE(SUM('SALES'[NET_SALES]),FILTER(ALLSELECTED('DATE_DIMENSION'),'DATE_DIMENSION'[Week of Year]<=MAX('DATE_DIMENSION'[Week of Year]))))

        Result:

         

        Best Regards,
        Zhu

         

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