topic Re: Calculate Price Allocation Based on one of instances in DAX Commands and Tips

Calculate Price Allocation Based on one of instances

RahulPBI — Thu, 13 Feb 2020 07:26:23 GMT

Hi Experts

I am fairly new to Power BI, but been learning a lot through here

Let me lay out the table and data, I have filtered the rows to show just one item

1. I have created a Stock Database which shows the details of an Item

2. I have a Table called Parent Component Raw Database where it shows in how many Parent Item Group Name the component is used. Thats why the multiple instances. In this case it repeats 37 times

I do the count in another table called Component Item Count

The help i need is to create a measure to the following from the diffrent tables

I want to calculate how much of the total price for the 15 on hand, can be allocated to the Parent Item Group Names

The measure I need is something like below

'Stock Database'[On Hand] / 'Component Item Count'[Count] * 'Stock Database'[Price]

this case would be 15 / 37 * 4.5193 = 1.8321

So in the report I could break down the cose by the Parent Item Group Name as below

L240 which has 16 instances will get allocated $29.3136

A240 which has 4 instances will get allocated $7.3284

O359 which has 2 instances will get allocated $3.6642

O379 which has 1 instances will get allocated $1.8321

RETRO which has 1 instances will get allocated $1.8321

ACCESS which has 1 instance will get allocated $1.8321

A740 which has 1 instance will get allocated $1.8321

SUNDRY SALE ITEM which has 1 instance will get allocated $1.8321

Component Items which has 2 instances will get allocated $3.6642

C140 which has 6 instance will get allocated $10.9926

L42 which has 2 instance will get allocated $3.6642

But when I try it as a measure i get the below error

Please help

Regards

Re: Calculate Price Allocation Based on one of instances

Greg_Deckler — Fri, 21 Feb 2020 21:14:47 GMT

It seems like you are using a measure but you are not wrapping an aggregation around column references. When you have a measure, if you reference a column, you have to use an aggregation like MAX, MIN, SUM, COUNT, AVERAGE, etc. If you are certain that in your visual you have filtered down to a particular row, you can generally use any of the aggregation functions except COUNT to grab your value. I tend to use MAX.

Re: Calculate Price Allocation Based on one of instances

Anonymous — Sat, 22 Feb 2020 13:25:36 GMT

To add to what Greg_Deckler said, you've got at least 2 redundant relationships in your model. I'd remove them. More, I'd consolidate the tables that are connected with a 1-to-1 relationship. The fewer tables you have in your model, the better. A model must be as simple as possible. Ideally, it should be a pure star schema. Also, bear in mind that all fields in a fact table should be hidden from view. Slicing should be done only through dimensions. If you don't follow this best practice, you'll sooner or later be producing wrong numbers without even knowing it. If you don't believe, then you should go to www.sqlbi.com and read some articles by The Italians.

Best

Re: Calculate Price Allocation Based on one of instances

RahulPBI — Sun, 23 Feb 2020 11:03:58 GMT

Hi Greg

I will try what you have suggested and let you know

if everything fails, will create a test Prix file and upload here

regards

Re: Calculate Price Allocation Based on one of instances

RahulPBI — Sun, 23 Feb 2020 11:06:36 GMT

Hi D I will read the information you advised Not sure which two redundant relationships, will try to upload a sample Pbix Regards

Re: Calculate Price Allocation Based on one of instances

Anonymous — Sun, 23 Feb 2020 14:47:22 GMT

RahulPBI, tables [* item count] and [* item lookup] should be one table (the star stands for parent or component). This simple transformation will remove 2 tables from your model and make it easier. Your model has many inactive relationships... I suspect it's therefore not optimal.

Best
D

Re: Calculate Price Allocation Based on one of instances

RahulPBI — Mon, 24 Feb 2020 12:19:09 GMT

Hi Greg & D

Finally after hours I've managed to create a test of the pbix

It was a challenege trying to get it as close as the original

Can you please look and advise if the attached pbix is ok, or I need to work on it a bit more

Regards

Re: Calculate Price Allocation Based on one of instances

RahulPBI — Mon, 24 Feb 2020 12:20:41 GMT

and i just realised i have no way to attach the file here

Re: Calculate Price Allocation Based on one of instances

RahulPBI — Mon, 24 Feb 2020 12:26:41 GMT

Greg_Deckler Anonymous Hope this work, please let me know if it doesn't

Sorry for all the trouble

https://1drv.ms/u/s!ApULo7UfaUptgS67MfEz-9TOBCUG?e=QNxX8O