I am attaching a source pbix which work in the context of a DAX measure taking a single percentage column and applying to a row whose keys match. See the “Target Split” measure added to accomplish that.
However, the source is more complex, and I’ve only gotten the table built as desired with a single % column now obsolete. I’ve attached a truer representation of the source file. The original “Split %” column is no longer a single column and I’ve labelled it “IGNORE”. The “Split %” by group is now reflected in five “column pairs” consisting of “Split Grp #” and “Split Group # %”. Thus, the Split Target amount (column) would be calculated based on matching the Project, Group (column B) and Period Start Date with the Split Group # value found in one of the five columns and using the adjacent (corresponding) Split Grp # % in the Split Target calculation.
In summary, we solved how to apply calculations to a “Team” row (or second row) of like records based on a single % column, but need more complex logic to “match” the Group (column B) and Split Group # on a Team=‘Y’ row and multiply that by the corresponding Split Grp % to concluded the proper Split Target (or Updated Target) amounts for all rows.
note: Like several source columns, the series of five column pairs for Splits exist on each row of the Project-Group-Period Start although the “Target” amount to which the percentages apply exists on only one row.
It also may be noteworthy that there may be more than two “Team” rows for the Project, Group, and Period Start Date key although the source file examples reflect only the two rows.
I’m sorry for not picking up on the approach to accomplish this, but I would really appreciate someone’s help in solutioning it. Happy to answer further questions.
I have come up with a Solution similar to earlier one . However, it is based on your sample data with an assumption that Split 1 will be for group 2a, Split 2 will be for 3a and so on,
If this is subject to change, then solution will also need changes. Have used your source file and created a new measure as below.
Target Split_New =
var Grp = max(Test[Group])
var Split_Perc = switch(Grp,“20a”,max(Test[Split Grp 1 %]),“30a”,max(Test[Split Grp 2 %]),“40a”,max(Test[Split Grp 3 %]))
VAR NoSplit = SUM( Test[Target] )
VAR HasSplit = SUMX( Test, Test[Updated Target] * DIVIDE( Split_Perc, 100 ))
RETURN
Unfortunately, the source data is more dynamic. A project is “allocated” to at least one and up to five groups. The company has roughly 20 groups. A unique row in the source requires Project (column A), Group (column B) and Period Start Date (column S). I’ve attached an updated source file with an additional project and its groups to illustrate why fixed values in the Split_Perc VAR cannot be used.
I’m sure what you’ve done is applicable, but requires more complex logic to match the Group (column B) value to one of the five “Split Grp #” columns in order to apply the corresponding ‘Split Grp # %’ value to Target_Split_New.
In the attached source file, I’ve appended three columns (highlighted in yellow) to reflect what is to be accomplished in the pbix. INDEX and MATCH are used to determine the proper Split Group % to use. In the end, I guess I’m looking to mimic the INDEX and MATCH logic in DAX based on the Updated Target measure created.
I appreciate the help.
As an update, it may be that the INDEX and MATCH is more appropriate to do in Power Query Editor although the Updated Target measure to which a percentage will be applied is done as a new measure in DAX.
I remain hopeful someone provides a solution to something I’m doing with INDEX and MATCH on the source spreadsheet, but in DAX or - more likely - Power Query Editor instead.
Please find the solution in attached file. It is entirely on Power Query and will need modifications as you add more groups but this is flexible and extendable. I can’t think of any DAX solution for now.
