Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello community,
I have a calculation group where I have a couple of calculation items, which perform a SUMX of a measure SUM(kg) and a referenced table with several columns, where I have the prices at a certain implementation date, e.g.
Jan21:
SUMX (
'BX_Table',
SELECTEDMEASURE()
* RELATED ( 'VRP Prices'[01.01.2021] )
)
Jun21:
SUMX (
'BX_Table',
SELECTEDMEASURE()
* RELATED ( 'VRP Prices'[01.06.2021] )
)
via a slicer on the calculation group, the user can chose the price applied to the SELECTEDMEASURE(kg) he wants to see. That works fine.
What I want to achieve now, is a DIVIDE or subtraction when two Calculation Items are chosen. I wouldn't mind if the formular breaks when more then two Calculation Items are chosen - in the first instance, I just want to show the % between the value June vs January.
My first attempt was at reading out the Calculation items used, but this does not seem to work. Has anyone an idea?
Thanks a lot!
Solved! Go to Solution.
I think I got the syntax for TREATAS wrong, try using VALUES( CalcGroupSlicer1[Name])
Hello @johnt75
thanks for your suggestion, however it won't work for me. I tried several things, but maybe I am misinterpreting your formular:
In the comparison measure, the TREATAS function always gives me an error. Is the Expression missing?
I have as a first snippet:
Comparison Measure =
VAR firstValue =
CALCULATE(
[Summe Gewicht kg], //this is my base measure, also used for Calculation Group
TREATAS(CalcGroupSlicer1[Name], ImplementaionDateSelection[Name])) //first element is underlined red, and following the syntax an expression is needed here. However, I cannot insert SELECTEDVALUE(CalcGroupSlicer1[Name]) so I don't know what expression is needed.
looking at your formular, it looks like you have two columns in your TREATAS, but this throws an error?
I think I got the syntax for TREATAS wrong, try using VALUES( CalcGroupSlicer1[Name])
You could create 2 new copies of your calculation group table just for use on the slicers.
Calc slicer 1 = VALUES('Calculation Group')
Calc slicer 2 = VALUES('Calculation Group')
Do not create any relationships from these tables, leave them totally disconnected.
You can then create measure to perform your division or subtraction or whatever like
Comparison measure =
var firstValue = CALCULATE( [Base measure], TREATAS( 'Calc slicer 1'[Calc item], 'Calculation Group'[Calc item]) )
var secondValue = CALCULATE( [Base measure], TREATAS( 'Calc slicer 2'[Calc item], 'Calculation Group'[Calc item]) )
return DIVIDE( firstValue, secondValue )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |