Need to append 2 sets of data with different granularity - not sure how to approach this

[peterg0417]

I've been tasked with mind-numbing assignment and I'm not sure how to proceed. Everything I've learned in the past few years tells me that this approach is wrong, but the powers that be don't seem to think so.

I basically have to append 2 tables with the same basic structure, but 1 table gives me the "Value" column down to Asset Class granularity, while the other does not. I asked for granularity down to Asset Class, but the business unit may not be willing to provide it. 

Here's an example of what I'm dealing with, and "Value - Adjusted" is a suggested workaround:

Any suggestions on how to tackle this? I can't even figure out how to create "Value - Adjusted" in Power Query or DAX. I'm at the point where I feel like the only paths forward are requesting the data at the correct granularity, or saying this isn't possible.

Thank you in advance

[PaulDBrown]

What is the structure of both tables before you append them?

Can you post samples of each?

[peterg0417]

Yep. here's 2 screens since I can't post the files from the looks of it.

When I unpivot the table in the second pic, the values in the first 4 columns repeat for each line of asset class. We currently don't have that level of granularity. It's not provided by that division, BUT the business wants to see asset value, or equity as a whole, but also break it down by Partner or Asset Class (as long as those values are provided for each asset class).

[PaulDBrown]

I meant actual data; not images!

[peterg0417]

what file types are supported? nethier xlsx or csv is allowed.

[PaulDBrown]

You can either copy and paste the data into a message or upload a file to a cloud service and share the link from there

[peterg0417]

https://drive.google.com/drive/folders/1gVM0cwCNNpe8KxjdNj_z116K23qTFJVf?usp=sharing 

let me know if this works.

[PaulDBrown · Accepted solution]

Thanks for the file. See if this helps you get closer to what you need.

For the table with the bad structure, you can split into two tables:

a) a table which has the fields present in the good structure table but lacks the "Asset class" field: add a column for "asset class" with a a value of "undefined":

You can then append this table with the good struture table:

b) unpivot the columns with the "asset class" in the "bad structure" table to form a new table as follows:

c) Create dimension tables for partner and asset class using the following:

Dim Asset Class =
DISTINCT (
    UNION (
        VALUES ( 'Single column'[Asset Class] ),
        VALUES ( 'Detailed columns'[asset class] )
    )
)

Dim Partner =
DISTINCT (
    UNION (
        VALUES ( 'Detailed columns'[Partner] ),
        VALUES ( 'Single column'[Partner] )
    )
)

 d) create the relationships:

e) Build the matrix using the fields from the dimension table and SUM measures for eahc value to get:

I've attached the sample PBIX file

[peterg0417]

This is amazing. Thank you! Over the weekend I started thinking of a similar solution, but you filled in some gaps for me with this solution. 

[rsbin]

@peterg0417 ,

Have an idea that may appease the Powers above.

For Partner 3, the current Value is approx 26% of the Total Invested (i.e. $5 / $19 M) or a 74% reduction.

Does it make sense to make an assumption that each Asset Class dropped by an equal amount?

Hence for Partner 3 - Industrial $4.9M * .26 = $1.27M.  Apply this to each Asset Class for this Partner and the Total then comes to $5M.

Let me know if this assumption can work...then we can proceed with next steps.

Regards,

[peterg0417]

I see where you're going with this, however it won't work. I had to change the numbers due to data confidentiality. The real numbers are different, and the Value column and Invested Amount columns have no relation. The original data structure is pivoted, so asset classes are their own columns going left to right. I unpivoted those columns to get the current structure since it makes more sense to work with the data like this in Power BI (where I can use Asset Class as a legend in a bar chart, for instance)

[rsbin]

@peterg0417 ,

Thanks for the clarification.  Unpivoting is definitely the right step.

My next idea.  Can you create an Asset Class called "All"? 
For Partners 3 and 6 the values would be $5 and $6.5.  For the others it would be a sum of the Asset Classes.

When creating your visuals, you will have the flexibility of showing All for all Partners or the Class Level for those Partners that have them.

[peterg0417]

Hmm....I think that could work.

Would this be dynamic? Say next quarter Partner 1 has an invested amount in a new asset class, but the business unit still won't provide me with the proper granularity, will this option dynamically update too? Or am I hard coding Partner 3 and 6 to be 5$ and $6.5 for all assset classes?

Either way though, that idea just might work

[rsbin]

@peterg0417 ,

Without knowing all your circumstances, I would approach this in the following way.

1. Create a Dimension table.  This would only have Partner ID, Partner Name and all other data elements associated with these entities.

2.  Keep your Fact tables separate.

2a) Create a Summary table for the ALL category.  So this table includes the Sum of All Asset Classes.

2b)  Keep your Detail table separate.  This table will not include Partner 3 or Partner 6

You can then join your Dimension table to these 2 fact tables.  Your data model is then set up to visualize as you see fit.

Lots of resources for how to create a Summary Table out there, but perhaps create a new thread if you think this direction works.

Best Regards,