How to group with calcs

Question

I have the data:&nbsp;DateInOutDepartment01/01/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.500,00&nbsp;Tech01/01/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.688,00&nbsp;Strategy01/01/2021&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.200,00Tech04/01/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 780,00&nbsp;Tech05/01/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 290,00&nbsp;Strategy06/01/2021&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.020,00Innovation07/01/2021&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18.500,00Global07/07/2021&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.700,00Global&nbsp;The normal result in Power BI is:DepartmentInOutGlobal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40.200,00Innovation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.020,00Strategy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.978,00&nbsp;Tech&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.280,00-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.200,00&nbsp;But i need other view too:DepartmentInOutInnovation + 1/3 Global&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20.420,00Strategy + 1/3 Global&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.978,00-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13.400,00Tech + 1/3 Global&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.280,00-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14.600,00&nbsp;How to aggregate this way?&nbsp;Thx in advance!

fowmy · Accepted Answer

gracizach&nbsp;Create two measures:
In Total =

VAR __GLOBAL = 
    CALCULATE(
        SUM(Table7[In]),
        Table7[Department] = "Global"
    )    
VAR _TOTAL = SUM(Table7[In])
RETURN
IF(
    MAX(Table7[Department] ) = "Global",
    BLANK(),
    IF(
            HASONEVALUE(Table7[Department]),
            _TOTAL + DIVIDE( __GLOBAL , 3 )
        ,
            _TOTAL
    )
)
Out Total = 
VAR __GLOBAL = 
    CALCULATE(
        SUM(Table7[Out]),
        Table7[Department] = "Global"
    )    
VAR _TOTAL = SUM(Table7[Out])
RETURN
IF(
    MAX(Table7[Department] ) = "Global",
    BLANK(),
    IF(
            HASONEVALUE(Table7[Department]),
            _TOTAL + DIVIDE( __GLOBAL , 3 )
        ,
            _TOTAL
    )
)

&nbsp;

Forum Discussion

How to group with calcs

1 Reply

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Calc not resulting in Percentage?!

Community User Group Experience

how to get totals in calc groups

Excel Calc in Power BI

Date	In	Out	Department
01/01/2021	1.500,00		Tech
01/01/2021	1.688,00		Strategy
01/01/2021		- 1.200,00	Tech
04/01/2021	780,00		Tech
05/01/2021	290,00		Strategy
06/01/2021		- 7.020,00	Innovation
07/01/2021		- 18.500,00	Global
07/07/2021		- 21.700,00	Global

Department	In	Out
Global	-	- 40.200,00
Innovation	-	- 7.020,00
Strategy	1.978,00
Tech	2.280,00	- 1.200,00

Department	In	Out
Innovation + 1/3 Global	-	- 20.420,00
Strategy + 1/3 Global	1.978,00	- 13.400,00
Tech + 1/3 Global	2.280,00	- 14.600,00