Distribution of values

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!

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.