Help to create a data model with Budget and Actuals

[Tarek78210]

Hi All,

I want to create a data model for the following:

I extract data from a software giving me the revenu for each product and customer. I have also a budget file I built that gives me the budgeted revenue per customer and product.

I want to create 4 tables:

- Product Group table that give all the possible product group in the revenue and budget : sometimes, we have a product group in the budget we haven't sold yet in the actuals. It is also true the other way. We sometimes sell products that were not budgeted.

- Customer Table gives all the possible customers in the revenue and budget. We have budgeted customers where no revenue is made and therefore will not appear in the actuals extract. The other way around is true when we have new customers not budgeted.

- Fact Table: where I have the extract from the tool giving me the actuals

- Fact Budget: my excel file showing the detailed budgeted revenu per customer and product

My biggest challenge is to create the product and customer tables . I want to base my liste of unique value from the actuals table AND the budget table to make sure I use all possible items.

Could you please help me with this?

[freshBI]

We would be happy to help out @Tarek78210! Check us out at https://www.freshbi.com/

[v-xinruzhu-msft · Accepted solution]

Hi @Tarek78210 

You can create two blank queries and input the following code to advanced editor 

Product

let
    actual_product=Table.FromList(actuals[ProductGroup]),
    budget_product=Table.FromList(budget[ProductGroup]),
    _combine=Table.Combine({actual_product,budget_product}),
    _distinct=Table.Distinct(_combine)
in
    _distinct

Output

Customer

let
    actual_custromer=Table.FromList(actuals[Customer]),
    budget_customer=Table.FromList(budget[Customer]),
    _combine=Table.Combine({ actual_custromer,budget_customer}),
    _distinct=Table.Distinct(_combine)
in
    _distinct

 Output

Best Regards!

Yolo Zhu

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