Forum Discussion
Helper IICalculation Groups (how to use it in table?)
I'm working with Power BI for a while now (6+ year) but never took the time to get to know the Calculation Groups. Now that it is also available in Power BI desktop I think it's time to at least get a good understanding on how it works and how it can be used. After playing around with it and reading some blogs I can see what it brings to the table but I'm strill struggling with how to use it when I want to combine multple measures with different items from the time intelligence group in one table.
As an example: what if I want a table with:
I could use the Calculation Group Time Intelligence for the different measures MTD, YTD, YOY, LY (Last year), FY(Full Year) which could be used on the Measures for Actuals, Budget and Forecast but how would I be able to get it in the table as shown here. I have only seen examples where all selected components of the time intelligense Calculated group are shown for all measures but that's not the requirement here.
Hi SnoekL ,
I understand your requirement in Power BI regarding calculation groups and the selection of measures and items.
Calculation groups allow you to define DAX expressions as calculation items that apply to existing measures in your model. They significantly reduce the need for redundant measures.
You can also read this document for some helps: Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn
You mentioned that the order of items is important. Unfortunately, Power BI doesn’t provide a direct way to control the order of calculation items within a calculation group. However, you can work around this by prefixing your calculation item names with numbers or other characters to enforce a specific order. For example:
“01. Item A”
“02. Item B”
“03. Item C”
This way, when you select items from the calculation group, they will appear in the desired order. You can read this document: Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- vanessafvg
Community Champion
not quite clear on what you saying, are you asking how you would place it in a visual?
so are you saying you would want to filter out some of the items and only show these?
please give an example of your expected solution as its not clear from what you have shown here.
would this video help? https://www.youtube.com/watch?v=vAnb3e1NCjM
SnoekLThats exactly what I mean. I won't to be able to select the measures and the calculation group items as shown in the table in my original post. Also the order of the items is important. In all video's blogs I have seen its selecting measures and items from the calculation groups which means the items selected are shown for the selected measures but that's not what I want.
- vanessafvg
just trying to understand what you are asking, i think i understand now, so the point of calcualtion items is to have set of reusable code, basically a function you can pass a measure to, most uses of this is just via filtering and placing it on a page, and you are saying this behaviour isnt what you want?
have you tried to play with as an fyi?
however you can hard code an calcualtion item in a measure, so in your measure you can reference
the code times for a variety of measures and no slicer or filter is required
this way you can just drag the measure on the page without having to navigate via the group slicer or by filtering.
so say you have a qtd calc item and you want qtd for the total sales measure
QTD Sales =
VAR calcitem = "Calendar QTD"
RETURN
CALCULATE ( [Total Sales], 'Calculation Group'[Name] = calcitem )this will then in a new measure pass total sales to the calculation group with the item name.
if this isn't what you are asking you might need to draw me a pic of your expected behaviour.
