Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SarikaKumari18
Helper III
Helper III

Build a line and stacked column chart using calculation groups

Hi everyone,
I am trying to use calculation groups for below reporting where CY and LY is slicer and chart is used as line & stacked column chart where line shows last year data whereas stacked column displays current year data.

Is there a way to achieve this using calculation groups so that it reduces the number of measures and we avoid using bookmark for this implementation ?
Currently, I can create chart with either CY or LY value but not together using calculation groups.
The purpose of using calculation groups here is to avoid bookmark and reduce the numbe of measures.

SarikaKumari18_0-1651478731535.png


Please share your idea or suggestion on this

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @SarikaKumari18 ,

 

Please create the following calculation groups, measures and calculation items in the Table Editor.

 

vkkfmsft_0-1651733446679.png

SelectMeasure1 = BLANK()
SelectMeasure2 = BLANK()
CYValue =
IF (
    ISSELECTEDMEASURE ( [SelectMeasure1] ),
    CALCULATE ( [Measure], DATESMTD ( 'Dates'[Date] ) ),
    SELECTEDMEASURE ()
)
LYValue =
IF (
    ISSELECTEDMEASURE ( [SelectMeasure2] ),
    CALCULATE ( [Measure], SAMEPERIODLASTYEAR ( Dates[Date] ) ),
    SELECTEDMEASURE ()
)

vkkfmsft_1-1651733641158.pngvkkfmsft_2-1651733661308.png

 

For more information, please refer to: https://www.youtube.com/watch?v=IH4bcHhxunw 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-kkf-msft
Community Support
Community Support

Hi @SarikaKumari18 ,

 

Please create the following calculation groups, measures and calculation items in the Table Editor.

 

vkkfmsft_0-1651733446679.png

SelectMeasure1 = BLANK()
SelectMeasure2 = BLANK()
CYValue =
IF (
    ISSELECTEDMEASURE ( [SelectMeasure1] ),
    CALCULATE ( [Measure], DATESMTD ( 'Dates'[Date] ) ),
    SELECTEDMEASURE ()
)
LYValue =
IF (
    ISSELECTEDMEASURE ( [SelectMeasure2] ),
    CALCULATE ( [Measure], SAMEPERIODLASTYEAR ( Dates[Date] ) ),
    SELECTEDMEASURE ()
)

vkkfmsft_1-1651733641158.pngvkkfmsft_2-1651733661308.png

 

For more information, please refer to: https://www.youtube.com/watch?v=IH4bcHhxunw 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thanks very much @v-kkf-msft , 
The solution works fine using single chart and bookmark is not needed. However, I have below concern to implement this 
- Sigma value is getting disappear for measures after using calculation groups which is known issue I belive
so I need to create measure for simple count/distinctcount of ID
-I should use two different column to show CY and LY as slicer and one should be always selected else chart will be blank (can't use one as single select and multi-select)
- For one measure ,the solution is fine but in case of other measure I will have to create seperate CYvalue and LYvalue measures so ending up creating seperate measures for each different KPI. For example :
one for count(ticketnumber) and other for sum(amount)

Thanks a lot again for your time 

Hi @SarikaKumari18 ,

 

You need to add new items to the original groups to show the count value.

 

vkkfmsft_0-1651742189451.png

CY Count =
IF (
    ISSELECTEDMEASURE ( [SelectMeasure1] ),
    CALCULATE ( [CountMeasure], DATESMTD ( 'Dates'[Date] ) ),
    SELECTEDMEASURE ()
)
LY Count = 
IF (
    ISSELECTEDMEASURE ( [SelectMeasure2] ),
    CALCULATE ( [CountMeasure], SAMEPERIODLASTYEAR ( Dates[Date] ) ),
    SELECTEDMEASURE ()
)

 

When no values are selected in the slicer, the line and stacked column chart shows actually the values of SelectMeasure1 and SelectMeasure2. If you don't want them to return blank, you can change the formula for SelectMeasure1 and SelectMeasure2 as needed, for example:

 

SelectMeasure1 = SUM ( 'Table'[Value] )

vkkfmsft_1-1651742280589.png

 

Best Regards,
Winniz

SarikaKumari18
Helper III
Helper III

Hello @amitchandak  , is it possible to make use of calculation groups when you have time slicer = CY and LY in report 
1. On selection of CY , bar chart with current year data should be shown 
2. On selection of LY, line and stacked bar chart should appear which will have both CY and CY value (x-axis=Month, Line y-axis= LY , column y-axis=CY )
please let me know if I am missing here something to achive the requirement

SarikaKumari18
Helper III
Helper III

@amitchandak , yes,I have created CY and LY member in calculation group but my question is how to plot line and stacked column chart when LY slicer is selected then column y-axis should show current year data and Line y-axis should show last year data in chart.

CY= SELECTEDMEASURE()
LY=
CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('CalendarTable'[Date]))

Measure=
COUNT('Incident'[Id])

amitchandak
Super User
Super User

@SarikaKumari18 , If you have created two members in the calculation group CY and LY, then you should be able to use it?

:Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

 

amitchandak_0-1651479886253.png

 

CY =

calculate(selectedmeasure(), datesytd('Date'[Date]))

PY =

calculate(selectedmeasure(), datesytd(dateadd('Date'[Date],-1,year)))

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.