The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
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
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
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.
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
User | Count |
---|---|
24 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
9 | |
9 |