Forum Discussion

sonuojha1's avatar
sonuojha1
Helper IV
5 years ago

Monthwise Line graph with multiple measures for multiple selected year - dual axes

Hello All,

I have a requirement to show line graph for all selected year from year slicer as new line on the graph for all months on x-axis.

I have two measures called M1 and M2 and two y-axis as shown in below screenshots.

attached below images for your reference.

 

 

 

Regards,

Sonu

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    sonuojha1 , you need to have 4 measures. as legends not supported with multiple meausres.

     

    You can use time intelligence

    example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

    • sonuojha1's avatar
      sonuojha1
      Helper IV
      5 years ago

      Thanks Amit for reply, 

      But none of the formula is giving correct result. one of the measures formula is as below:

      Result ( tried with your YTD formula- TimeSeriesEfficiency This Year)

       

       

      Actual calculation formula 

      Effici =
      VAR V_X=[Ther]*3412
      VAR V_Y=[Total Wa]*100
      var v_sum=IF(ISBLANK(V_X),0,V_X)+IF(ISBLANK(V_Y),0,V_Y)
      VAR V_RES=IFERROR(V_SUM/[Total G]/1000,0)
      RETURN
      IF(ISBLANK(V_RES) || V_RES=0,BLANK(),V_RES)

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Could you share some data and show the expected result in a Table format?  Once the numbers in the Table appear correct, one can build any visual.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi sonuojha1 ,

     

    Line Chart is not supported for legend with multiple values. You will need to create measures for each line. Please show some sample data if you what us to help deal with DAX formula.

     

    Best Regards,

    Jay