Combining Measures and Spreading Proportional Values based on % of whole

[efstel]

I have a group of costs that are allocated throughout 6 areas, however some of those costs are not allocated to any area at all. I want to distribute the unallocated costs proportionally between the 5 or 6 areas based on each areas overall total divided by the sum total of all 6 areas.

I created a measure to determine the % allocation.

%JCIDBilledAmt to OfficeName =

VAR _TotalJCIDBilledAmt =

    CALCULATE(

        [BilledAmt],

        All(Offices[OfficeName]) )

RETURN

DIVIDE(

    [BilledAmt],

_TotalJCIDBilledAmt)

 Now… What I want to do is take the $740,992 and multiply it by the 24.67%,… so on, so that amount is spread proportionally over each of the OfficeName.

I ran this formula but it’s not giving me the amount for each area and only giving me a sum total.

GLDTAmount for <Filter> Part3 Null x %JCIDBilledAmt to OfficeName =

[GLDTAmount for <Filter> Part3 Null] * [%JCIDBilledAmt to OfficeName]

How do I get that $740,992 spread proportionally?

What I also need to do is add the SumAmount to the spread proportioned amount together.

Any help or insight would be appreciated.

[efstel]

@Anonymous still not working for me.  I have three tables. One has office information, one billing information, one overhead information. I can't get it to spread the unallocated overhead based on the %ofBilledAmounttoOffice.

[Anonymous]

Hi, @efstel 

I looked at your screenshot and you have a bit of a lot of data, can you provide a PBIX file that doesn't contain private data so that I can better write a DAX expression that works for you.

Best Regards

Jianpeng Li

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

[efstel]

I currently don't have the ablity to upload files. (Not a superuser.) Nevertheless, here's a link:

https://www.dropbox.com/scl/fi/trewbdpor34y47jhsw2fz/Unallocated-Test.pbix?rlkey=8rdr7nvjktwhv2vuy95...

[Anonymous · Accepted solution]

Hi, @efstel 

Thank you very much for the data. I got the output you were expecting using the following DAX expression:

Measure 3 = 
VAR _unallocated_ =  CALCULATE(SUM(OverheadTable[OverheadAmount]),FILTER(ALLSELECTED(OfficeTable), 'OfficeTable'[Office]=BLANK()))
VAR _officename_ = SELECTEDVALUE(OfficeTable[Office])
VAR _prop_ = [%BilledAmounttoOffice]
RETURN IF(_officename_=BLANK(),_unallocated_,_unallocated_*_prop_)

Here are the results:

I've uploaded the PBIX file I used this time below.

Best Regards

Jianpeng Li

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

[efstel]

@Anonymousworks great! Only one small problem. When I filter or slice it by an office, it drops the unallocated amount out. Any way to keep that in? I'm assuming I need an ALL function in there?

What I'm doing in addition is adding the newly created measure to the overhead amount to give me the overall overhead. Unallocated Overhead + Allocated Overhead = Total Office Overhead

[Anonymous]

Hi, @efstel 

Yes, you may need to clear the slicer's selection to keep the unallocated amount. You can ask this new question in a new post so that the rest of the community can help you.

Best Regards

Jianpeng Li

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

[Anonymous]

Hi, @efstel 

Based on your description, I tried to create a table like yours:

I created a measure using the following DAX expression:

GLDTAmount for <Filter> Part3 Null x %JCIDBilledAmt to OfficeName=
VAR _No_allocation =
    CALCULATE (
        SELECTEDVALUE ( Offices[Amount] ),
        'Offices'[OfficeName] = BLANK ()
    )
VAR _office_name =
    SELECTEDVALUE ( Offices[OfficeName] )
VAR _Proportional = [%JCIDBilledAmt to OfficeName]
RETURN
    IF ( _office_name = BLANK (), _No_allocation, _No_allocation * _Proportional )

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[efstel]

Thanks @Anonymous. The one problem is that there is no [Amount] in the 'Offices' table. I tried switching out Offices[Amount] with GLDT[Amount] and I get no results.

Any ideas how to remedy this?

[Anonymous]

Hi, @efstel 

Thank you very much for your reply. Based on your description, I've created a new table as follows:

The relationship is as follows:

The DAX expression is as follows:

GLDTAmount for <Filter> Part3 Null x %JCIDBilledAmt to OfficeName = 
VAR _No_allocation = CALCULATE(SELECTEDVALUE('Table'[Amount]),'Offices'[OfficeName]=BLANK())
VAR _office_name = SELECTEDVALUE(Offices[OfficeName])
VAR _Proportional = [%JCIDBilledAmt to OfficeName]
RETURN IF(_office_name=BLANK(),_No_allocation,_No_allocation*_Proportional)

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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