topic Re: Combining Measures and Spreading Proportional Values based on % of whole in DAX Commands and Tips

Combining Measures and Spreading Proportional Values based on % of whole

efstel — Wed, 12 Jun 2024 17:12:58 GMT

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.

Re: Combining Measures and Spreading Proportional Values based on % of whole

Anonymous — Thu, 13 Jun 2024 03:05:42 GMT

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.

Re: Combining Measures and Spreading Proportional Values based on % of whole

efstel — Thu, 13 Jun 2024 10:20:20 GMT

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?

Re: Combining Measures and Spreading Proportional Values based on % of whole

Anonymous — Mon, 17 Jun 2024 02:51:10 GMT

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.

Re: Combining Measures and Spreading Proportional Values based on % of whole

efstel — Wed, 19 Jun 2024 19:59:26 GMT

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.

Re: Combining Measures and Spreading Proportional Values based on % of whole

Anonymous — Fri, 21 Jun 2024 10:31:00 GMT

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.

Re: Combining Measures and Spreading Proportional Values based on % of whole

efstel — Fri, 21 Jun 2024 18:50:01 GMT

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=8rdr7nvjktwhv2vuy95qiyete&st=ly1u94xr&dl=0

Re: Combining Measures and Spreading Proportional Values based on % of whole

Anonymous — Mon, 24 Jun 2024 06:47:24 GMT

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.

Re: Combining Measures and Spreading Proportional Values based on % of whole

efstel — Tue, 25 Jun 2024 20:43:20 GMT

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

Re: Combining Measures and Spreading Proportional Values based on % of whole

Anonymous — Wed, 26 Jun 2024 05:27:32 GMT

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.