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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lherbert501
Helper V
Helper V

Calculation Groups - Difference measures

Hi,

 

Could somebody please help me whether this is possible?

 

I need the layout like below to be in a matrix. I've used a calculation group for current and previous period and this works well because they are using the same fields.

 

My problem is that Growth has some different calculations to the other two, and as this all needs to be on the same matrix I'm struggling to put it together. 

 

Could somebody please advise if this is possibele?

 

Thanks in advance

 

lherbert501_0-1707656122499.png

 

1 ACCEPTED SOLUTION

@lherbert501 

output 

Daniel29195_0-1707769742433.png

 

 

link to file : 

https://drive.google.com/file/d/1ZGTRZGdnUbNmPqFTGsyRbRJ6rv1aJgMq/view?usp=sharing

 

 

let me know if this works for you .

 

 

 

If this answers your question  ,  mark it as the solution so can you can help  other people in the community find it easily .

 

View solution in original post

13 REPLIES 13
Daniel29195
Super User
Super User

@lherbert501 

 

sorry but the image is somewhat confusing .( for me maybe ) . 

you want to have 2 levels in the columns : 
level1 : 

growth current period vs previous perd ,

previous period

current period 

 

level2 : 

growth %, growth $ 

sales , profit 

sales , profit 

 

Hi @Daniel29195 ,

 

Currently its coming out below which is correct, but how would I add two more columns onto the growth but not onto the other two (Current Period & Previous Period).

 

Growth needs to have 4 columns on the matrix and current period and previous need to have two.

 

lherbert501_0-1707727870331.png

 

lherbert501_1-1707727983925.png

 

 

@lherbert501 

maybe you can do the following : 
create a table with the following structure : 

 

level 1                                         level2 

growth                                      total sales 

growth                                       total margin

growth                                       calculation 3

growth                                       calculation 4

previous period                            total sales 

previous period                            total margin

current period                                total sales 

current period                              total margin

 

 

then in your calculation you can use one measure with switch statement 

 

 

 

 

Hi @Daniel29195 ,

 

Apologies I'm a little confused. When you say table with levels, do this mean with calculation groups?

 

Thanks again

 

Liam

@lherbert501 

table using enter data  manually .

this way you are creating the structure of your table how you want it to be . 

 

and then you play with the measure and switch to change the calculation behavior base on the column you are in . 

 

 

 

The best I've managed is to get both measures to display as blank in a matrix by using this in tabular editor

 

lherbert501_1-1707747073003.png

 

 

IF( SELECTEDMEASURENAME() in {"Cost1", "Cost2"}, BLANK(), SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[DateFull])) )

 

 Thanks @Daniel29195 . And this would still come out grouped like below?

lherbert501_0-1707727870331.png

 

@lherbert501 

yes sure.

if you can, share some sample data, and i will do it for you . 

Hi @Daniel29195 

 

Thank you so much.  

 

I hope that's okay what I've attached. I've used shapes to show what I require on the page

 

Sample Data.pbix

@lherbert501 

output 

Daniel29195_0-1707769742433.png

 

 

link to file : 

https://drive.google.com/file/d/1ZGTRZGdnUbNmPqFTGsyRbRJ6rv1aJgMq/view?usp=sharing

 

 

let me know if this works for you .

 

 

 

If this answers your question  ,  mark it as the solution so can you can help  other people in the community find it easily .

 

Thanks @Daniel29195  for your time with this. This works. 

 

With this setup, is it possible to had another column outside of the top heading like below, or even if the top level was blank. 

 

lherbert501_0-1707817513571.png

 

@lherbert501 

Daniel29195_0-1707817687129.png

 

 

isnt this what you mean ? 
( the ones in blue circles ) 

Hi @Daniel29195 

 

I'm not sure if it replied to the right reply, but If I put it like in the example below. It would need to be in values to display as a solid column in the matrix but for example a customer having a start date that would need to show for each customer going down, but it wouldnt necessarily be in any of the 3 levels.

 

lherbert501_0-1707819083310.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors