Distribution of values

[LoryMenCR]

Dear All,

i have the following fact table 1:

Code	Desc	Date	Value
3	Code 3 - Desc 1	02/01/2023	100
3	Code 3 - Desc 2	02/01/2023	50
5	Code 5 - Desc 1	02/01/2023	200
7	Code 7 - Desc 1	04/01/2023	300
7	Code 7 - Desc 2	05/01/2023	600
9	Code 9 - Desc 1	12/01/2023	800
100	Code 100 - Desc 1	13/01/2023	1000
100	Code 100 - Desc 2	14/01/2023	1000
100	Code 100 - Desc 3	14/01/2023	500

I also have a table 2 which defines my distinct codes:

Code	Code Name
3	Name 3
5	Name 5
7	Name 7
9	Name 9
100	Name 100

What i need is to distribute the sum of value of Code 100 on a monthly basis throughout all the other codes.

In order to accomplish this I have a table 3 where I define the percentages of allocation of code 100 values through the other codes:

Start_Code	End_Code	%
100	3	50
100	5	20
100	7	20
100	9	10

Result should be the following:

Code	Desc	Date	Value
3	Code 3 - Desc 1	02/01/2023	100
3	Code 3 - Desc 2	02/01/2023	50
3	100		1250
5	Code 5 - Desc 1	02/01/2023	200
5	100		500
7	Code 7 - Desc 1	04/01/2023	300
7	Code 7 - Desc 2	05/01/2023	600
7	100		500
9	Code 9 - Desc 1	12/01/2023	800
9	100		250

Any idea?
I don't know if I should accomplish this through powerquery or DAX...
Thanks a lot

[123abc · Accepted solution]

Here’s how you can do it using DAX:

1. Create a measure for the total value of Code 100: First, you need to calculate the sum of the values associated with Code 100.

TotalValueCode100 =
CALCULATE(
SUM('Table1'[Value]),
'Table1'[Code] = 100
)

Define a DAX measure to distribute Code 100's total value according to percentages: Now you can create a measure that distributes the total value of Code 100 based on the percentages in Table 3.

DistributedValue =
VAR Code100Value = [TotalValueCode100]
VAR CurrentCode = MAX('Table1'[Code])
VAR AllocationPercent =
LOOKUPVALUE(
'Table3'[%],
'Table3'[End_Code], CurrentCode,
'Table3'[Start_Code], 100
)
RETURN
IF(ISBLANK(AllocationPercent),
SUM('Table1'[Value]),
Code100Value * AllocationPercent / 100
)

• This measure looks up the percentage from Table 3 based on the current code and multiplies it by the total value of Code 100.

• Adjust the table visualization:

  • Add the Code and Desc columns to your visual.
  • Include the Date and use the DistributedValue measure in the "Values" field.

• Filter Code 100 in your result set: If you want the result where Code 100 is distributed only to the other codes, you can filter Code 100 out of the original list using the following DAX:

FilteredCodes =
FILTER(
'Table1',
'Table1'[Code] <> 100
)

This will give you the final result, distributing Code 100's values across other codes according to the percentage breakdown in Table 3. Let me know if you'd like a Power Query solution instead!

[LoryMenCR]

Dear @123abc ,

thanks! This solution might work for me, I'm doing some testing.

An issue lies here:

DistributedValue =
VAR Code100Value = [TotalValueCode100]
VAR CurrentCode = MAX('Table1'[Code])
VAR AllocationPercent =
LOOKUPVALUE(
'Table3'[%],
'Table3'[End_Code], CurrentCode,
'Table3'[Start_Code], 100
)
RETURN
IF(ISBLANK(AllocationPercent),
SUM('Table1'[Value]),
Code100Value * AllocationPercent / 100
)

LOOKUPVALUE scans my table1 for codes.

Thing is: there i the possibility that, for a certain month, costs won't be produced for some code in table1. But the measure should allocate costs from code 100 to ALL the end_codes.

Example [Missing code 7 in February 2024]:

Code	Desc	Date	Value
3	Code 3 - Desc 1	20/02/2024	500
3	Code 3 - Desc 2	23/02/2024	1000
5	Code 5 - Desc 1	25/02/2024	200
9	Code 9 - Desc 1	27/02/2024	300
100	Code 100 - Desc 1	29/02/2024	1000
100	Code 100 - Desc 2	29/02/2024	500

How the measure works now [No allocation for code 7]:

Code	Desc	Date	Value
3	Code 3 - Desc 1	20/02/2024	500
3	Code 3 - Desc 2	23/02/2024	1000
3	100		750
5	Code 5 - Desc 1	25/02/2024	200
5	100		300
9	Code 9 - Desc 1	27/02/2024	300
9	100		150

How the measure should work [Allocation also for code 7]:

Code	Desc	Date	Value
3	Code 3 - Desc 1	20/02/2024	500
3	Code 3 - Desc 2	23/02/2024	1000
3	100		750
5	Code 5 - Desc 1	25/02/2024	200
5	100		300
7	100		300
9	Code 9 - Desc 1	27/02/2024	300
9	100		150

[v-kaiyue-msft]

Hi @LoryMenCR ,

You can create a calculated column in Table 1.

Column = 
VAR _sum100 =
    CALCULATE (
        SUM ( 'Table 1'[Value] ),
        FILTER ( ALL ( 'Table 1' ), 'Table 1'[Code] = 100 )
    )
VAR _value =
    SUMX (
        'Table 3',
        IF ( 'Table 3'[End_Code] = 'Table 1'[Code], _sum100 * 'Table 3'[%] / 100, 0 )
    )
RETURN
    _value

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[123abc · Accepted solution]

Here’s how you can do it using DAX:

1. Create a measure for the total value of Code 100: First, you need to calculate the sum of the values associated with Code 100.

TotalValueCode100 =
CALCULATE(
SUM('Table1'[Value]),
'Table1'[Code] = 100
)

Define a DAX measure to distribute Code 100's total value according to percentages: Now you can create a measure that distributes the total value of Code 100 based on the percentages in Table 3.

DistributedValue =
VAR Code100Value = [TotalValueCode100]
VAR CurrentCode = MAX('Table1'[Code])
VAR AllocationPercent =
LOOKUPVALUE(
'Table3'[%],
'Table3'[End_Code], CurrentCode,
'Table3'[Start_Code], 100
)
RETURN
IF(ISBLANK(AllocationPercent),
SUM('Table1'[Value]),
Code100Value * AllocationPercent / 100
)

• This measure looks up the percentage from Table 3 based on the current code and multiplies it by the total value of Code 100.

• Adjust the table visualization:

  • Add the Code and Desc columns to your visual.
  • Include the Date and use the DistributedValue measure in the "Values" field.

• Filter Code 100 in your result set: If you want the result where Code 100 is distributed only to the other codes, you can filter Code 100 out of the original list using the following DAX:

FilteredCodes =
FILTER(
'Table1',
'Table1'[Code] <> 100
)

This will give you the final result, distributing Code 100's values across other codes according to the percentage breakdown in Table 3. Let me know if you'd like a Power Query solution instead!

[LoryMenCR]

Dear @123abc ,

thanks! This solution might work for me, I'm doing some testing.

An issue lies here:

DistributedValue =
VAR Code100Value = [TotalValueCode100]
VAR CurrentCode = MAX('Table1'[Code])
VAR AllocationPercent =
LOOKUPVALUE(
'Table3'[%],
'Table3'[End_Code], CurrentCode,
'Table3'[Start_Code], 100
)
RETURN
IF(ISBLANK(AllocationPercent),
SUM('Table1'[Value]),
Code100Value * AllocationPercent / 100
)

LOOKUPVALUE scans my table1 for codes.

Thing is: there i the possibility that, for a certain month, costs won't be produced for some code in table1. But the measure should allocate costs from code 100 to ALL the end_codes.

Example [Missing code 7 in February 2024]:

Code	Desc	Date	Value
3	Code 3 - Desc 1	20/02/2024	500
3	Code 3 - Desc 2	23/02/2024	1000
5	Code 5 - Desc 1	25/02/2024	200
9	Code 9 - Desc 1	27/02/2024	300
100	Code 100 - Desc 1	29/02/2024	1000
100	Code 100 - Desc 2	29/02/2024	500

How the measure works now [No allocation for code 7]:

Code	Desc	Date	Value
3	Code 3 - Desc 1	20/02/2024	500
3	Code 3 - Desc 2	23/02/2024	1000
3	100		750
5	Code 5 - Desc 1	25/02/2024	200
5	100		300
9	Code 9 - Desc 1	27/02/2024	300
9	100		150

How the measure should work [Allocation also for code 7]:

Code	Desc	Date	Value
3	Code 3 - Desc 1	20/02/2024	500
3	Code 3 - Desc 2	23/02/2024	1000
3	100		750
5	Code 5 - Desc 1	25/02/2024	200
5	100		300
7	100		300
9	Code 9 - Desc 1	27/02/2024	300
9	100		150

[123abc · Accepted solution]

The problem arises because LOOKUPVALUE is only returning results for codes present in Table1. To overcome this, we need to create a structure where all possible End_Code values are accounted for, even if they are missing in the fact table for a particular period.

Here’s how you can address this in Power BI:

Modified Approach Using DAX

1. Generate All Codes for Allocation:

First, we need to create a logic that considers all End_Code values from Table 3 regardless of whether they exist in the current filter context of Table1.

2. Use a Virtual Table to Force the Allocation:

You can modify your DistributedValue measure to include all codes from Table 3 by creating a virtual table that ensures every End_Code has an allocation from Code 100, even if some are missing in Table1.

DistributedValue =
VAR Code100Value = [TotalValueCode100] -- Sum of Code 100 values
VAR AllocationTable =
FILTER (
'Table3',
'Table3'[Start_Code] = 100 -- Ensure it's Code 100 allocation
)
VAR Result =
SUMX (
AllocationTable,
VAR CurrentCode = 'Table3'[End_Code]
VAR AllocationPercent = 'Table3'[%]
RETURN
IF (
ISBLANK(AllocationPercent),
0, -- No allocation if no percent is defined
Code100Value * AllocationPercent / 100
)
)
RETURN
IF (
MAX('Table1'[Code]) = 100, -- Ensure allocation only affects other codes, not Code 100 itself
BLANK(),
Result
)

Changes in This Measure:

1. Allocation for Missing Codes: By using SUMX on AllocationTable, this ensures all End_Code values (from Table 3) are included in the allocation, regardless of whether they exist in Table1 for the selected date or period.
2. Handling BLANK Values: For any codes not present in Table1, the measure will still allocate the corresponding percentage from Code 100 based on Table 3.
3. Preventing Duplication: The IF(MAX('Table1'[Code]) = 100) check ensures that allocations happen only for other codes and not for Code 100 itself.