Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Please share your idea or suggestion on this
Solved! Go to Solution.
Hi @SarikaKumari18 ,
Please create the following calculation groups, measures and calculation items in the Table Editor.
SelectMeasure1 = BLANK()
SelectMeasure2 = BLANK()
CYValue =
IF (
ISSELECTEDMEASURE ( [SelectMeasure1] ),
CALCULATE ( [Measure], DATESMTD ( 'Dates'[Date] ) ),
SELECTEDMEASURE ()
)
LYValue =
IF (
ISSELECTEDMEASURE ( [SelectMeasure2] ),
CALCULATE ( [Measure], SAMEPERIODLASTYEAR ( Dates[Date] ) ),
SELECTEDMEASURE ()
)
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.
Hi @SarikaKumari18 ,
Please create the following calculation groups, measures and calculation items in the Table Editor.
SelectMeasure1 = BLANK()
SelectMeasure2 = BLANK()
CYValue =
IF (
ISSELECTEDMEASURE ( [SelectMeasure1] ),
CALCULATE ( [Measure], DATESMTD ( 'Dates'[Date] ) ),
SELECTEDMEASURE ()
)
LYValue =
IF (
ISSELECTEDMEASURE ( [SelectMeasure2] ),
CALCULATE ( [Measure], SAMEPERIODLASTYEAR ( Dates[Date] ) ),
SELECTEDMEASURE ()
)
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.
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] )
Best Regards,
Winniz
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
@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])
@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
CY =
calculate(selectedmeasure(), datesytd('Date'[Date]))
PY =
calculate(selectedmeasure(), datesytd(dateadd('Date'[Date],-1,year)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |