Line chart, keeping one of the x-axis for reference of the other x-axis value

[tspoon]

Dear all,

Another help needed for Line Chart.

How to keep one the x-axis which showing 15 weeks Plan value (0-100%) while the other x-axis showing value of the the selected week from the slicer?

Here’s the goal expected:

This page showing values of the first to fifth week if we select 5th week from the slicer:

(right image: hovering to the last Actual value)

This page showing values of the first to sixth week if we select 6th week from the slicer:

(right image: hovering to the last Actual value)

Many thank's in advance.

[Anonymous · Accepted solution]

Hi @tspoon 

My solution is as follows, you can try it out.

1. Here are the columns included in my example table. The “plan” and “actual” here represent the percentage.  
2. Create a new table using the following DAX : Table2 = SELECTCOLUMNS('Table',"week",'Table'[week], "weeknum", 'Table'[weeknum])  
3. Create a measure for actual progress using the following DAX: ActualMeasure = CALCULATE(SUM('Table'[actual]), FILTER('Table', 'Table'[weeknum] <= MAX('Table2'[weeknum])))  
4. Create a line chart, put the “week” of the “Table” table into the X-axis, and the “plan” of the “Table” table and the new metrics created in the previous step into the Y-axis. Create a slicer, put the “week” of the “Table” table into the X-axis, and the “plan” of the “Table” table into the Y-axis. Table2” into Field.  
5. Select the measure used in the Y-axis and format it to percentage under the Measure tools tab.  
6. Here is my final test result.   

   I hope this solution meets your requirements.

   ^{Best Regards,}

   ^{Jarvis Tang}

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

    

[tspoon]

Hi @Anonymous ,

Thanks for solution, it's really help me to understand the concept.

But still there is one problem. After I add W10 as new data, the line chart (x-axis) is not sorting well, even after I set "weeknum" for  "sort by column" in the "table view". 

[tspoon]

Found it!

Select "three dots (more options)" from the visual, select "sort axis" then select the "plan" and also select "ascending".

Thanks