SUMX grand total

[Dellis81]

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!

[amitchandak]

@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

[Dellis81]

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!

[v-kelly-msft · Accepted 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.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Dellis81]

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 )
    ))

[v-kelly-msft]

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

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[parry2k]

@Dellis81 no offense but too much to read. It will be easier if you provide sample data and expected output.