Forum Discussion
Total Sum by Product Difficulty
So to give you a potential explanation of what the measure do i'll tell you the following:
the average of 10+12 = 22 meaning average is 22/2 = 11
This is what happens when you use the measure
AVERAGE(Warehouse_and_Retail_Sales[RETAIL SALES])The column is the only variable used in the calculation. When creating a visual the calculation is partinioned by the product group as seen in scenario 3.With the other measure you do the following:you calculate the Sum of sales which for example REF = 380.You then divide 380 by the number of times it shows up. However in this case only 380 is known so it does: 380/1 = 380. meaning the average shown is 380.The method i mentioned earlier does 10+ 12 = 22 / 2 = 12 Average.Something similar is happening with SUMX()What we usually do in SQL and DAX is calculate over a column: RETAIL_SALES and then partition it over a group. in SQL we do GROUP BY and in Power BI this is done automatically by selecting different components in a visualKR,--Troekoe
Thank you , I've tried that and it's working as you've illustrated. I am also now curious how to do the measure as I've edited my question above. Any ideas?
Yo!
The issue is that in your DAX query:
Sales Amount by Items =CALCULATE([Total Retail Sales],FILTER(Warehouse_and_Retail_Sales, Warehouse_and_Retail_Sales[ITEM TYPE]))
The second staement in the FILTER() function should be an expression. For isntanceWarehouse_and_Retail_Sales[ITEM TYPE] = "X". Where X is the name of a certain group.Else it is a statement boolean in itsself e.g. True/False.As mentioned before this wouldn't apply to you as you are already trying to divide it by group which you can do with a visual as i shown in my earlier comment.In order for me to explain more in depth please provide examples of your data and how your models looks.Also try to explain to me what the end goal is: a table/visual etc.Hope this helps!--TroekoeFor the average you can do the same:
Measure = AVERAGE(Your_table[Sales])Thanks so much for your clear explanations. So I've included the summary data and I'm getting three different results: I'll show the 3 different measures, including the average measure you've just shown me.
ITEM TYPE Sales Amount by Item Type WINE €746,498.59 BEER €574,220.53 LIQUOR €802,691.43 STR_SUPPLIES €2,740.88 KEGS €0 REF €663.63 DUNNAGE €0 NON-ALCOHOL €34,084.31 1)
Average Sale by Item Type =AVERAGEX(VALUES(Warehouse_and_Retail_Sales[ITEM TYPE]), [Total Retail Sales])RESULTS:2)
Total Sales by Item Type =SUMX(VALUES(Warehouse_and_Retail_Sales[ITEM TYPE]), SUMX(Warehouse_and_Retail_Sales, Warehouse_and_Retail_Sales[RETAIL SALES]))RESULT:3) As suggested in your response:
Average Item Retail Sales =AVERAGE(Warehouse_and_Retail_Sales[RETAIL SALES])RESULT:Each method shows a different result for average, and for the end total.
I'm really confused.
So to give you a potential explanation of what the measure do i'll tell you the following:
the average of 10+12 = 22 meaning average is 22/2 = 11
This is what happens when you use the measure
AVERAGE(Warehouse_and_Retail_Sales[RETAIL SALES])The column is the only variable used in the calculation. When creating a visual the calculation is partinioned by the product group as seen in scenario 3.With the other measure you do the following:you calculate the Sum of sales which for example REF = 380.You then divide 380 by the number of times it shows up. However in this case only 380 is known so it does: 380/1 = 380. meaning the average shown is 380.The method i mentioned earlier does 10+ 12 = 22 / 2 = 12 Average.Something similar is happening with SUMX()What we usually do in SQL and DAX is calculate over a column: RETAIL_SALES and then partition it over a group. in SQL we do GROUP BY and in Power BI this is done automatically by selecting different components in a visualKR,--Troekoe