Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I have a report where I have multiple type of "years" I am attempting to sum net income across entity divisions - where a specific year crop division may span multiple calendar years. For example -2019 corn spans calendar year 2018-2020. Within this same report - I have divisions (ie financing) where we want only those dollars represented with 2019.
In this example - I do have the correct net income within the calendar and production year columns, but obviously the GRAND TOTAL is wrong.
I have a slicer to harvest the year value from the ProjectGroupSetup[ProdYear] - so if 2019 is selected, I want all 2019 crops (which spans multiple calendar years) + those divisions with a designited calendar yr for the year 2019.
This first measure is my initial attempt using an IF statement to determine which calculate([Net Income], and filter based on selected year).
ExecYr NetMargin Test =
VAR SelectedYr =
SELECTEDVALUE ( ProjectGroupSetup[ProdYear] )
VAR CurrentDivisionYr =
MAX ( Division[ExecYr] )
VAR ReportValue =
IF (
CurrentDivisionYr = "CalYr",
CALCULATE (
[BS Net Income],
ALL ( ProjectGroupSetup[ProdYear] ),
'CALENDAR'[Year] = SelectedYr
),
CALCULATE (
[BS Net Income],
ALL ( 'CALENDAR' ),
ProjectGroupSetup[ProdYear] = SelectedYr
)
)
RETURN
ReportValue
This second measure was my attempt at using the SUMX across "divisions" thinking I could eventually use this value as total. I end up with the same result as shown above
ExecYr NetMargin Test2 =
VAR SelectedYr =
SELECTEDVALUE ( ProjectGroupSetup[ProdYear] )
VAR ReportValue =
SUMX (
Division,
VAR CurrentDivisionYr =
MAX ( Division[ExecYr] )
RETURN
IF (
CurrentDivisionYr = "CalYr",
CALCULATE (
[BS Net Income],
ALL ( ProjectGroupSetup[ProdYear] ),
'CALENDAR'[Year] = SelectedYr
),
CALCULATE (
[BS Net Income],
ALL ( 'CALENDAR' ),
ProjectGroupSetup[ProdYear] = SelectedYr
)
)
)
RETURN
ReportValue
Again, the individual values are correct - the grand total is not. My eventual goal is to remove the division as a column header - reflecting a proper grand total at the bottom.
I truly appreciate the support offered on the forum. thank you!
Solved! Go to Solution.
Hi @Dellis81 ,
Create 2 measures as below:
Measure = SUMX(VALUES('Division'[Division]),'CALENDAR'[ExecYr NetMargin Test])
Measure2 = SUMX(VALUES('Division'[Division]),'CALENDAR'[ExecYr NetMargin Test2])
And you will see:
For the updated .pbix file,pls see attached.
@Dellis81 , You have to try a new measure like
sumx(summarize(Division, Division[ExecYr], "_1",[ExecYr NetMargin Test]),[_1])
or
sumx(summarize(Division,Division[Division], Division[ExecYr], "_1",[ExecYr NetMargin Test]),[_1])
you may have to add other group bys in summarize
Thank you both for responding 🙂
I've tried both ideas, still not quite there. So, I have tried to "dumb" down the live file, and have a more simplified example to show. Here is a one Drive link
https://1drv.ms/u/s!AmBVCme14p7xlVa2IR8lDSI2XNhu?e=btu7qg
Image below illustrates the results of the two measures previously provided - total line in yellow should be $1,264,497.44 for the year 2019
PS - sorry, as I was manipulating data to pull in, I failed to get a bigger value in the financing division. But, I think you can work with what I have.
Appreciate your help!
Hi @Dellis81 ,
Create 2 measures as below:
Measure = SUMX(VALUES('Division'[Division]),'CALENDAR'[ExecYr NetMargin Test])
Measure2 = SUMX(VALUES('Division'[Division]),'CALENDAR'[ExecYr NetMargin Test2])
And you will see:
For the updated .pbix file,pls see attached.
YES!!! Thank You!
I was able to successfully transfer your measure into the larger live file - AWESOME!
I think I was close to doing the same thing - except I was trying to put the IF statements inside the SumX. You took hte measure containing the IF - and placed inside the SumX. What is the difference? Below measure is my attempt at taking your measure -and replacing with what I thought should work. I don't want to take much of your time on this - it's more my attempt to understand?
Thank You!
ExecYr NetMargin Test3 =
VAR SelectedYr =
SELECTEDVALUE ( ProjectGroupSetup[ProdYear] )
return
SUMX(VALUES('Division'[Division]),
VAR SelectedYr =
SELECTEDVALUE ( ProjectGroupSetup[ProdYear] )
VAR CurrentDivisionYr = max(Division[ExecYr])
return IF ( CurrentDivisionYr = "CalYr",
CALCULATE([BS Net Income],ALL(ProjectGroupSetup[ProdYear]),'CALENDAR'[Year]=SelectedYr),
CALCULATE([BS Net Income],ALL('CALENDAR'),ProjectGroupSetup[ProdYear]=SelectedYr )
))
Hi @Dellis81 ,
It's a grandtotal issue which is a common issue.
Try to create a measure as below:
_Sum=SUMX(ALLSELECTED([diviation]),[ExecYr NetMargin Test2])
Here is the reference:
https://community.powerbi.com/t5/Desktop/SUMX-grand-total/td-p/1269630
@Dellis81 no offense but too much to read. It will be easier if you provide sample data and expected output.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |