Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

cummulative value without affected by slicer

Hello There

I need some help with my report.

i have two tables in excel which is as my source of my BI report. one contain dates, monthly achievement, running cummulative monthly achievement, monthly target and running cummulative monthly target, and the other one is only list of dates. this table of list of dates is used as slicer to filter many charts within my BI report. so these tables have connections in my BI between dates on list of data to dates on list of dates

list of dateslist of data

the report is like this picture below, the slicer that contains name of month data came from table list of dates (as i mentioned earlier, this slicer is to filter many chart in one report page), and the combo chart data came from table list of data. line chart is cummulative target, and bar chart is cummulative monthly achievement

report chart

i want to slice the chart by slicer on the above but only affect to the cummulative monthly achievement (bar chart), for instance, if i click month of january on the slicer, the bar chart should show only for january, and the february one is not shown, and so on, and the line chart should remain the same, still showing from month of january to december (not affected by slicer's filter).

 

maybe there's some DAX or measures required to do this, i've try to make simple measure which calculate monthly achievement to running total (like using calculate, sum, filter etc.), but still not working as i wanted. the slicer keep slicing line chart

filtered chart

 

what i expected after i do some slicer filter is like this (this is easy in excel) 

 please if anybody in this forum can help me

 

Thank you BI Forum

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    i think it is hard to resolve this, if the x-axis value and the slicer value both come from date table.

    if not, can try to remove the filters of slicers in the line measure.

    =CALCULATE([Target Value],REMOVEFILTER(Slicers[Month]))

    • Anonymous's avatar
      Anonymous
      Not applicable

      thanks, after two months the result is i have to build separate tables and it worked