Forum Discussion

ValentinHESS's avatar
ValentinHESS
New Member
1 year ago
Solved

Combine Line & column chart with Calculation Group

Hello everyone

 

I am currently trying to use for the first time the claculation group within my financial report.

I created a lot of calculation items 

But I have a problem when I want to create a line & column chart.

In fact I want to display in line both YTD and YTD N-1 and the variation in % on the column part such like this:

But I don't know how to use correctly the calculation group within the build section (below with measures)

 

Could you please help me with it ?

 

Thank you

 

Regards

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ValentinHESS 

    Based on your description, if you want to display the line & column chart, it is better that you create a new measure instead of using calculation group, you can try the following.

    There is a calendar table, there is a 1:N reationship between the tables.

    Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

    Here is the sample measures i used.

    Count = COUNTROWS('Table')
    Sum = CALCULATE(SUM('Table'[Value]))

    1.Create a new table to put the measure names to the table.

    2.Create the following measures.

    MTD =
IF (
    ISFILTERED ( 'Table (2)'[MeasureName] ),
    SWITCH (
        SELECTEDVALUE ( 'Table (2)'[MeasureName] ),
        "Count", CALCULATE ( [Count], DATESMTD ( 'Calendar'[Date] ) ),
        "Sum", CALCULATE ( [Sum], DATESMTD ( 'Calendar'[Date] ) )
    )
)

    YTD =
IF (
    ISFILTERED ( 'Table (2)'[MeasureName] ),
    SWITCH (
        SELECTEDVALUE ( 'Table (2)'[MeasureName] ),
        "Count", CALCULATE ( [Count], DATESYTD ( 'Calendar'[Date] ) ),
        "Sum", CALCULATE ( [Sum], DATESYTD ( 'Calendar'[Date] ) )
    )
)

    Then create the visual, and put the ytd and mtd measues to the visual.

    Then create a slicer and put the measurename to the slicer.

     

    Output

    And there is another way, you can consider to use two visuals :line chart and column chart so that the calculation group can be put to the legend of the line chart visual.

     

    Best Regards!

    Yolo Zhu

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ValentinHESS 

    Based on your description, if you want to display the line & column chart, it is better that you create a new measure instead of using calculation group, you can try the following.

    There is a calendar table, there is a 1:N reationship between the tables.

    Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

    Here is the sample measures i used.

    Count = COUNTROWS('Table')
    Sum = CALCULATE(SUM('Table'[Value]))

    1.Create a new table to put the measure names to the table.

    2.Create the following measures.

    MTD =
IF (
    ISFILTERED ( 'Table (2)'[MeasureName] ),
    SWITCH (
        SELECTEDVALUE ( 'Table (2)'[MeasureName] ),
        "Count", CALCULATE ( [Count], DATESMTD ( 'Calendar'[Date] ) ),
        "Sum", CALCULATE ( [Sum], DATESMTD ( 'Calendar'[Date] ) )
    )
)

    YTD =
IF (
    ISFILTERED ( 'Table (2)'[MeasureName] ),
    SWITCH (
        SELECTEDVALUE ( 'Table (2)'[MeasureName] ),
        "Count", CALCULATE ( [Count], DATESYTD ( 'Calendar'[Date] ) ),
        "Sum", CALCULATE ( [Sum], DATESYTD ( 'Calendar'[Date] ) )
    )
)

    Then create the visual, and put the ytd and mtd measues to the visual.

    Then create a slicer and put the measurename to the slicer.

     

    Output

    And there is another way, you can consider to use two visuals :line chart and column chart so that the calculation group can be put to the legend of the line chart visual.

     

    Best Regards!

    Yolo Zhu

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