Forum Discussion
Calculation group - how to make this working - example model attached.
Hi jaryszek ,
Ths is how calcaulation items work, no matter what calculation you have you will get the value on the calculation item in this case you have the following calculation items:
Daily = 999 Monthly = 10000000Being a calculation Group whateve measure you apply this the result will be 999 or 10000000 so let's do this step by step.
No calculation item applied:
In this case the measure returns monthly or Daily has you can see I have not selecteced any value from the Granularity slicer.
Selecting Monthly Daily Value:
When I select the monthly value since this is a measure the value of that measure is overwritten by 999
When I select monthly it gets the 10M
Basically you are overwritting the calculation and making it a new calculation.
For the calculations group to work properly you can use the additional syntax:
SELECTEDMEASURE
ISSELECTEDMEASURE
SELECTEDMEASUREFORMATSTRING
SELECTEDMEASURENAME
All of this allow you to tell on top of what the calculation is going to be performed if you just pickup a specific measure on your calculation group that value would always be used with that measure however using the syntax above you can do it for a specific calculation or for all of them.
For example if my code was:
Daily = IF(ISSELECTEDMEASURE([Date Hierarchy Selection]), SELECTEDMEASURE(), 999)For this measure Date Hierachy Selection it will always return the maximum value of the calculation group in this case Monthly:
That is the reason why the previous calculation groups I have sent you were:
Monthly = m = SELECTEDMEASURENAME() RETURN SWITCH( TRUE(), m = "Total Amortized Cost", [Total Amortized Cost (Monthly)], SELECTEDMEASURE() )In this I check if the measure is "Total Amortized Cost", if it is I return the monthly calculation otherwise I return the value of the measure.
One more question.
How power bi knows that here It needs to trigger Calculation Group which is called "Granularity" in our case?
There is the measure within chart only, not calculation group...
Best,
Jacek
Hi jaryszek ,
Calculations groups are applied on top of the measures that are within the context of that calculation group in this case the slicer with the calculation group is sending the correct context to the chart and making the calculation change accordingly.
Thank you,
one more question:
you added this one -
and this is Max from DateHierarchy so i suppose 1 value like Daily or Monthly from DateHierarchy field parameter:
When I am trying to debug it:It is not showing Daily or Monthly, it is showing Value from Monthly/Daily calculation groups items.
Why?
Why this is replaced here ? It is a usual behaviour?
Best,
JacekHi jaryszek ,
Ths is how calcaulation items work, no matter what calculation you have you will get the value on the calculation item in this case you have the following calculation items:
Daily = 999 Monthly = 10000000Being a calculation Group whateve measure you apply this the result will be 999 or 10000000 so let's do this step by step.
No calculation item applied:
In this case the measure returns monthly or Daily has you can see I have not selecteced any value from the Granularity slicer.
Selecting Monthly Daily Value:
When I select the monthly value since this is a measure the value of that measure is overwritten by 999
When I select monthly it gets the 10M
Basically you are overwritting the calculation and making it a new calculation.
For the calculations group to work properly you can use the additional syntax:
SELECTEDMEASURE
ISSELECTEDMEASURE
SELECTEDMEASUREFORMATSTRING
SELECTEDMEASURENAME
All of this allow you to tell on top of what the calculation is going to be performed if you just pickup a specific measure on your calculation group that value would always be used with that measure however using the syntax above you can do it for a specific calculation or for all of them.
For example if my code was:
Daily = IF(ISSELECTEDMEASURE([Date Hierarchy Selection]), SELECTEDMEASURE(), 999)For this measure Date Hierachy Selection it will always return the maximum value of the calculation group in this case Monthly:
That is the reason why the previous calculation groups I have sent you were:
Monthly = m = SELECTEDMEASURENAME() RETURN SWITCH( TRUE(), m = "Total Amortized Cost", [Total Amortized Cost (Monthly)], SELECTEDMEASURE() )In this I check if the measure is "Total Amortized Cost", if it is I return the monthly calculation otherwise I return the value of the measure.