Calculate cost per product per user from budgeted data

PV 21

Hi,
I have a requirement where i have the following data model and unable to arrive a correct measure for calculating cost per product per user.I want to be also able to slice by date and empl dim.
Can someone pls correct and post ideas to achieve above.Attached is a sample models.I have 2 models and both are not allowing me to achieve what i want.

The second model merges 2 tables :Usage and HC.This has 7m rows and allows me to get correct HC filtered by date dim but i am not getting the cost
Any help on this please??

Ronen Ariely 15,096 Reputation points

2020-08-18T05:09:35.547+00:00

Good day @PV

Attached is a sample models.

Where?!?

I only see image which does not help us to reproduce the scenario and provide a solution

Please provide DDL to create the tables and relevant entities + DML of sample data
PV 21 Reputation points

2020-08-18T12:47:57.437+00:00

The above model is attached here and created a sample PBI model to play with.
view
PV 21 Reputation points

2020-08-21T19:18:31.023+00:00

Hi,
I have the sample data in the powerBI sample model attached here.Pls post your ideas

1 answer

Lukas Yu -MSFT 5,816 Reputation points

2020-08-18T08:28:52.06+00:00

Could we slice the cost as this:
= SUMMARIZECOLUMNS('Date'[Date], 'Empl'[Country], "Budget", [Budget])
Please sign in to rate this answer.
PV 21 Reputation points

2020-08-21T19:20:14.397+00:00

The problem is i dont want to create a calculated table like this because i want to slice by different fields of the Empl Dimension.

Lukas Yu -MSFT 5,816 Reputation points

2020-08-28T05:51:57.583+00:00

If we slice the table directly in the browser this is the dax auto generate behind the scene, not creating calculated table
Sign in to comment

Share via

Calculate cost per product per user from budgeted data

1 answer