Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding 2 measures into one measure

Hi Everyone,

 

I have 2 measures Actual & Projected . Actual from Table A & Projected is from Table B.
Table A have data till today, Table B have values till end of the month.
My requirement is to show both cumulative of Actual & Projected by month in one line chart.
Till today it should display cumulative of actual and from tomorrow same line should show cumulative of Projected . Both the tables have relationship on date_id and Table A is in relation with calendar used in slicer.
Actual - Solid line
Projected - Dotted line
Can any one help on this.
 
Please find the example chart below for reference
 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    @Sriram_K, one way is that you put them together so you won't get line of points for the target

    Cumm ? CALCULATE(SUM(Table1[Amount]) + SUM(Table2[forecast]),filter(allselected(date),date[date] <-maxx(date,date[date])))

    O
    Cumm : CALCULATE(SUM(Table1[Amount]) + SUM(Table2[forecast]),filter(allselected(date),date[date] <-max(Sales[Sales Date])))

    another way put them as a separate measure

    Cumm AmountTM CALCULATE(SUM(Table1[Amount]) ,filter(allselected(date),date[date] <-maxx(date,date[date])))

    Cumm forecastTM CALCULATE( SUM(Table2[forecast]),filter(allselected(date),date[date] <-maxx(date,date[date])))

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    @Sriram_K, one way is that you put them together so you won't get line of points for the target

    Cumm ? CALCULATE(SUM(Table1[Amount]) + SUM(Table2[forecast]),filter(allselected(date),date[date] <-maxx(date,date[date])))

    O
    Cumm : CALCULATE(SUM(Table1[Amount]) + SUM(Table2[forecast]),filter(allselected(date),date[date] <-max(Sales[Sales Date])))

    another way put them as a separate measure

    Cumm AmountTM CALCULATE(SUM(Table1[Amount]) ,filter(allselected(date),date[date] <-maxx(date,date[date])))

    Cumm forecastTM CALCULATE( SUM(Table2[forecast]),filter(allselected(date),date[date] <-maxx(date,date[date])))

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

    I create two measures to display the actual and projected values. Then put them in the same line chart as values and format the chart through Format -> Shapes -> Customize series. At last, I get the chart as shown on the bottom.

     

    Actual =
CALCULATE (
    SUM ( Table1[Actual Amount] ),
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( Table1[Date] )
    )
)


Actual Display =
IF ( MAX ( 'Calendar'[Date] ) <= TODAY (), [Actual], BLANK () )


Projected Display = 
IF (
    MAX ( 'Calendar'[Date] ) <= TODAY (),
    BLANK (),
    [Actual]
        + CALCULATE (
            SUM ( Table2[Projected Amount] ),
            FILTER (
                ALLSELECTED ( 'Calendar'[Date] ),
                'Calendar'[Date] > TODAY ()
                    && 'Calendar'[Date] <= MAX ( Table2[Date] )
            )
        )
)

     

     

    You can download the sample pbix here.

     

    Best Regards,

    Community Support Team _ Jing Zhang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jing,

      Thanks for your help, it's not working for me my case getting only actival values, and need cumulative values by month.

      Thanks & Regards,

      Sriram.K

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

        If only actual values display, it is probably because the projected values are not calculated correctly and get all the results as blank. Could you provide some sample data or .pbix file so that I can modify the measures accordingly? Please remove any sensitive information. Thanks.

        Regards,

        Jing