Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Lets take the following tables
Table 1 - Invoices
Date, Supplier, Invoice Nbr, Item Code, Qty, Price, Line Value
01/02 S1 1234 ABC 5 20.00 100.00
01/02 S2 1235 ABC 5 21.00 105.00
01/02 S1 1236 XYZ 5 20.00 100.00
01/02 S1 1237 XYZ 5 20.00 100.00
01/02 S1 1238 ABC 5 19.00 95.00
01/02 S1 1239 ABC 5 18.00 90.00
Table 2 - Suppliers
Table 3 - Items
The ultimate goal is to count how many times the supplier offered a price above the total average or the Item.
So in the above instance...
- Item ABC has an average Buy Price of 88/4 =22
So I want to count how many times the buy price for each supplier was above (or below) that average.
I thought to do it I would need a flag on each row in the table that determined whether that buy price was above or below the average.
Could someone please assist as I am going around in circles on this.
I have measures that calculate the Total Average for the Item, and another for specific suppliers.
What I am battling with is flagging each row in a way that tells me whether it as greater or less than the average buy price.
Do I assume I need a Measure or Calculated column on the Stock table for Averge Buy price.
And then a calculated column in the Invoices table that compares the price field to the Stock measure ?
Any thoughts greatly appreciated.
Solved! Go to Solution.
Hi @shaunwilks
check the file: https://1drv.ms/u/s!AiiWkkwHZChHj0_9NlI0Luaj1J2D
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @shaunwilks
check the file: https://1drv.ms/u/s!AiiWkkwHZChHj0_9NlI0Luaj1J2D
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks for the time in doing that.
I worked on it yesterday evening and ended up getting a similar result but clearly not in such a clean way as you have achieved it.
Ill use your code as a way to educate myself.
Above Average Count = SUMX( VALUES( Items[ItemCode] ), VAR AVGPrice = CALCULATE( AVERAGE( Invoices[Price] ), ALL( Suppliers ) ) RETURN CALCULATE( COUNTROWS( Invoices ), Invoices[Price] > AVGPrice ) )
I created a calculated column on the Item table for the fixed Price avg.
Avg Buy Price = CALCULATE(AVERAGEX(RELATEDTABLE('Invoices'),'Invoices'[Price])
I then had a calculated column in the invoices table to do the comparison.
Just a snippet below but extended it to match exact requirements.
Price Status = IF('Invoices'[Price]>Related('Items'[Avg Buy Price]),"Over", "Under")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |