cancel
Showing results for 
Search instead for 
Did you mean: 
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)))

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors