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.
Have a table of item sales by division. Each division is selling the same items at somewhat different pricing. Would like to show how much division sales are above/below the Total Avg. No trouble getting this into Power BI, or getting the Total Avg column. I can summarize all the data I'm after in visualizations, but am having trouble creating the Division product average that can then be used in further calculations....like the Difference field. New to Power BI, Many Thanks.
Please see below for what I'm using to the the 'Total Avg' measure. The table at bottom is an idea of what I'm after.
Total Avg = SUM(Sheet1[Sales]) / SUM(Sheet1[Qty]).
Solved! Go to Solution.
Oh so nothing calculated yet, gotcha. You will need a couple of measures:
Total Qty = SUM(Table1[Qty])
Average price = SUMX(Table1,Table1[Qty]*Table1[Price])/[Total Qty]
Average for company = CALCULATE([Average price],ALL(Table1[Division]),VALUES(Table1[Product]))
Difference = IF(HASONEVALUE(Table1[Division]),[Average for company]-[Average price],blank())
the HASONEVALUE bit is not completely necessary but its the bit behind the blanks at the total line on the matrix i have shown.
// if this is a solution please mark as such
Something along the lines of:
COMPANY AVERAGE = CALCULATE( [DIVISION AVERAGE] , ALL(SHEET1[DIVISION NAME]) , VALUES(SHEET1[PRODUCT]) )
// If this is a solution please mark as such
Thanks! I'm working with the table at top and trying to replicate something similar to what is at bottom. I'm working to create the Division Avg field, this isn't currently part of table in BI.
Oh so nothing calculated yet, gotcha. You will need a couple of measures:
Total Qty = SUM(Table1[Qty])
Average price = SUMX(Table1,Table1[Qty]*Table1[Price])/[Total Qty]
Average for company = CALCULATE([Average price],ALL(Table1[Division]),VALUES(Table1[Product]))
Difference = IF(HASONEVALUE(Table1[Division]),[Average for company]-[Average price],blank())
the HASONEVALUE bit is not completely necessary but its the bit behind the blanks at the total line on the matrix i have shown.
// if this is a solution please mark as such
Thanks! This is what I'm after. Your help is much appreciated.
Regards- rovercon
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |