Need help with conditional formula with a combo of max and sum

[PBINovice123]

Hi,

I have a formula that I'm trying to figure out but so far have been unsuccessful.  

I'm trying to calculate the total budget across all departments, using the following logic: 

Sum of all Total Budgets Per Dept ID where Total Budget Per Dept ID = Max of Shared Budget + Separate Budget

For example, the total budget for Dept ID 54321 is 300,000 (the max of the shared budgets of 200,000 plus the separate budget of 100,000).  

Dept ID	Structure	Budget	Employee
12345	Separate	100,000	Kim
12345	Separate	100,000	John
54321	Shared	100,000	Chris
54321	Shared	200,000	Ashley
54321	Separate	100,000	Kelly

Any help would be appreciated!  Thanks so much.  

[v-zhangti · Accepted solution]

Hi, @PBINovice123 

Please try the following methods.

Total budget =
CALCULATE (
    MAX ( 'Table'[Budget] ),
    FILTER (
        'Table',
        [Dept ID] = EARLIER ( 'Table'[Dept ID] )
            && [Structure] = "Shared"
    )
)
    + CALCULATE (
        SUM ( 'Table'[Budget] ),
        FILTER (
            'Table',
            [Dept ID] = EARLIER ( 'Table'[Dept ID] )
                && [Structure] = "Separate"
        )
    )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ribisht17]

@PBINovice123 

You can replace Null easily with 

For the first one I am not facing such issues, maybe you have more number of columns there, I need to see the exact sample data-set

Also please check again if you are giving the filter below

Max Shared Budget per ID = CALCULATE(max(Sheet2[Budget]),ALLEXCEPT(Sheet2,Sheet2[Dept ID ],Sheet2[Structure]),Sheet2[Structure]="Shared")

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

[PBINovice123]

Thanks a lot, but unfortunately it still isn't computing as intended. 

One thing I noticed is that the "Shared Budget per ID" formula is giving me a value even if none of the structures within the field are "Shared". 

Additionally, I failed to mention that some of the values in the "Structure" column are blank and should be treated like a Separate structure.  Do you know how I would account for all of this? 

Thanks again!

[ribisht17]

@PBINovice123 

Step 1

Max Shared Budget per ID = CALCULATE(max(Sheet2[Budget]),ALLEXCEPT(Sheet2,Sheet2[Dept ID ],Sheet2[Structure]),Sheet2[Structure]="Shared")

Step 2

Separate Budget per ID = CALCULATE(max(Sheet2[Budget]),ALLEXCEPT(Sheet2,Sheet2[Dept ID ]),Sheet2[Structure]="Separate")

Solution=[Max Shared Budget per ID]+ Sheet2[Separate Budget per ID]

Here is my video on ALL,ALLEXCEPT function https://youtu.be/fEIaJigVfFg if you wish to see

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users