Data Modeling Ideas / Complex Lookups

[a68tbird]

Hello All -

   Looking for some suggestions on how I should model my data. Here's the scenario.

I prepare quarterly statements for our clients in Excel, but I'd like to create a similar report with PowerBI.  The data source is common for all clients, with similar products sold, but each client has very particular deal splits.  With some clients, the deal might be a 15% on Gross Revenue, and then 70/30 on net; with others it's a straight 65/35 on net; some, it's a static amount ($1) per unit of product sold.  There are 15 clients, with just as many types of products sold through each client's sites.  There must be a better, more efficient way of accomplishing what I'd like to do without using nested IF statements (ie. IF client = x and productType = a, then use this DealSplitFormula; ElseIf etc etc etc). 

Any ideas? Maybe you had a similar challenge and figured it out?

Thanks very much.

Travis

[itchyeyeballs]

I'd look at creating a lookup table with the relevant ratios, you could use it when loading data to create calculated values. Would be easy to then adjust the values as needed. Join it on unique client id.

[a68tbird]

Thanks for the suggestion @itchyeyeballs.  I've created a lookup table with client IDs and each product type with the relevant rates.  I've used this approach in an Excel sheet before, but it required a multi lookup.  For instance, in the Excel version, the formula is:

INDEX(_dealSplits,MATCH($B20,_productType,0),MATCH($A$11,_propertyID,0))

where _dealSplits is the named range of percentages; _productType the named range of product types; and _propertyID the named range of client IDs.  This returns the relevant percentage, which I could then use to calculate the client share by multipling the Gross Revenue.

Would this be replicable with DAX?

[itchyeyeballs]

Hi @a68tbird

It can be done in Dax but would it be more feasible to do it in the data load? PowerQuery (M) lets you specify multiple columns to merge on, you could then create a column with the relevant value from the lookup.

If you need to do it in Dax I would start off by creating a single combined index field in the data and use that to match.