Forum Discussion
Help with Calculation Groups
I've been away from the Community for a while. Need to get back to it. Never had the occasion to use Calculation Groups, but think I have a Use Case for it now. Looking for guidance. Working in an Analysis Services Tabular model with extensive Sales, Forecast, and Budget data. Users want the ability to switch between KG and LBS. The current model has most if not all the Measures already developed for both Units separately. Parameter functionality is unavailable due to it being an AS model. I could write an enormous amount of SWITCH Measures, but hoping Calculation Groups would be more effective and efficient. Appreciate thoughts, comments, and guidance on how I would set this up
Example: Invoiced Volume = SWITCH( True(),
[Selected Unit] ="KG", [Invoiced Volume KG],
[Invoiced Volume LBS] )
Hi ramnikb,
Yes, this is a textbook calculation group use case. And since KG to LBS is a fixed factor, you can go one step further: keep only the KG measures and let one calculation item do the conversion. That removes the duplicate LBS measures instead of wrapping them in SWITCH.
table "Unit" (calculation group, column "Unit") item "KG" -> SELECTEDMEASURE() (ordinal 0, the default) item "LBS" -> SELECTEDMEASURE() * 2.20462 (only for volume measures) slicer on Unit[Unit], single select -> every volume measure followsThe important part is to scope the conversion, so revenue, prices and % measures pass through untouched:
-- Calculation item: LBS VAR IsVolume = ISSELECTEDMEASURE ( [Invoiced Volume], [Forecast Volume], [Budget Volume] ) -- or by naming convention: -- CONTAINSSTRING ( SELECTEDMEASURENAME (), "Volume" ) RETURN IF ( IsVolume, SELECTEDMEASURE () * 2.20462, SELECTEDMEASURE () ) -- Format String Expression on the LBS item (optional unit label) IF ( CONTAINSSTRING ( SELECTEDMEASURENAME (), "Volume" ), "#,0 \"lbs\"", SELECTEDMEASUREFORMATSTRING () )If some LBS measures aren't a straight conversion (rounding rules, different source columns), map them explicitly instead of multiplying:
-- Calculation item: LBS (mapping variant) SWITCH ( SELECTEDMEASURENAME (), "Invoiced Volume KG", [Invoiced Volume LBS], "Forecast Volume KG", [Forecast Volume LBS], SELECTEDMEASURE () )A few practical notes for an AS model:
compatibility level 1500+ is required for calculation groups author it in Tabular Editor or Visual Studio, then deploy if you also add time intelligence groups, set Precedence -> the unit conversion should apply after YTD/PY logic no slicer selection (or several items selected) -> no item is applied -> measures fall back to plain KG; use a single-select slicer hide the old *_LBS measures from report authors once migratedSo my overall approach would be:
one "Unit" calculation group: KG (passthrough) + LBS (x 2.20462) -> scope with ISSELECTEDMEASURE / name convention -> optional format string expression for the unit suffix -> single-select slicer -> retire the duplicate LBS measuresAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
5 Replies
- rsbin
Community Champion
Good morning jubinsoni and ashubrar_ ,
Thank you for the replies. Will work through this over the next couple of days to see if I can get everything working properly.
Kind Regards, 
jubinsoniSolution Sage
Hi ramnikb,
Yes, this is a textbook calculation group use case. And since KG to LBS is a fixed factor, you can go one step further: keep only the KG measures and let one calculation item do the conversion. That removes the duplicate LBS measures instead of wrapping them in SWITCH.
table "Unit" (calculation group, column "Unit") item "KG" -> SELECTEDMEASURE() (ordinal 0, the default) item "LBS" -> SELECTEDMEASURE() * 2.20462 (only for volume measures) slicer on Unit[Unit], single select -> every volume measure followsThe important part is to scope the conversion, so revenue, prices and % measures pass through untouched:
-- Calculation item: LBS VAR IsVolume = ISSELECTEDMEASURE ( [Invoiced Volume], [Forecast Volume], [Budget Volume] ) -- or by naming convention: -- CONTAINSSTRING ( SELECTEDMEASURENAME (), "Volume" ) RETURN IF ( IsVolume, SELECTEDMEASURE () * 2.20462, SELECTEDMEASURE () ) -- Format String Expression on the LBS item (optional unit label) IF ( CONTAINSSTRING ( SELECTEDMEASURENAME (), "Volume" ), "#,0 \"lbs\"", SELECTEDMEASUREFORMATSTRING () )If some LBS measures aren't a straight conversion (rounding rules, different source columns), map them explicitly instead of multiplying:
-- Calculation item: LBS (mapping variant) SWITCH ( SELECTEDMEASURENAME (), "Invoiced Volume KG", [Invoiced Volume LBS], "Forecast Volume KG", [Forecast Volume LBS], SELECTEDMEASURE () )A few practical notes for an AS model:
compatibility level 1500+ is required for calculation groups author it in Tabular Editor or Visual Studio, then deploy if you also add time intelligence groups, set Precedence -> the unit conversion should apply after YTD/PY logic no slicer selection (or several items selected) -> no item is applied -> measures fall back to plain KG; use a single-select slicer hide the old *_LBS measures from report authors once migratedSo my overall approach would be:
one "Unit" calculation group: KG (passthrough) + LBS (x 2.20462) -> scope with ISSELECTEDMEASURE / name convention -> optional format string expression for the unit suffix -> single-select slicer -> retire the duplicate LBS measuresAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
calculation groups are exactly what you need here. they are built specifically to prevent measure explosion and will completely replace the need for all those switch statements.
instead of maintaining separate kg and lbs measures, you just need one base measure for each metric. then the calculation group intercepts it and applies the conversion on the fly using the selectedmeasure function.
here is how to set it up:
create a new calculation group called "unit selector".
create two calculation items inside it: "kg" and "lbs".
assuming your base measures are in kg, the dax for your items is super simple.
for the kg item type: SELECTEDMEASURE()
for the lbs item type: SELECTEDMEASURE() * 2.20462once saved and deployed, just drop the new "unit selector" column into a slicer on your canvas. whatever base measure is on the visual (sales, forecast, budget) will automatically convert when the user toggles the slicer. you can even set dynamic format strings for each item if you want the "lbs" or "kg" suffix to appear in the visual.
if this helps you get it working, please feel free to mark this as the accepted solution so others can find it.
v-aatheequeCommunity Support
Hi ramnikb
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
- v-aatheeque
Hi ramnikb
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.
