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 REFE...
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.

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.

      • fazou's avatar
        fazou
        Frequent Visitor
        1 year ago

        Anonymous  thank you for help,can you explain more your solution please ? or put the pbix if it's possible, thank you 🙂