Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
RahulPBI
Helper I
Helper I

Calculate Price Allocation Based on one of instances

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

 

Capture.JPG

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

Capture1.JPG

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

 

Capture2.JPG

I do the count in another table called Component Item Count

Capture3.JPG

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

Capture4.JPG

 

Please help

Regards

 

 

8 REPLIES 8
Anonymous
Not applicable

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

D

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

@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

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

 

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

 

 

@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

 

Greg_Deckler
Community Champion
Community Champion

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.