Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SnoekL
Helper II
Helper II

Calculation 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:

SnoekL_0-1709369039365.png

 

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.

1 ACCEPTED SOLUTION

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.

vyilongmsft_1-1709523351741.png

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

vyilongmsft_2-1709523943986.png

 

 

 

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.

View solution in original post

4 REPLIES 4
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thats 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.

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.

vyilongmsft_1-1709523351741.png

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

vyilongmsft_2-1709523943986.png

 

 

 

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.

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.