Forum Discussion

MandoMongo's avatar
MandoMongo
Regular Visitor
2 years ago
Solved

Total Sum by Product Difficulty

* EDIT: I think I have it but perhaps not working as it should   Average Sale by Item Type = AVERAGEX(     VALUES(Warehouse_and_Retail_Sales[ITEM TYPE]), [Total Retail Sales] )   Total Sale b...
Need Help
  • Troekoe's avatar
    Troekoe
    2 years ago

    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 visual
     
    KR,
     
    --Troekoe
     
MandoMongo's avatar
MandoMongo
Regular Visitor
2 years ago

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?

Troekoe's avatar
Troekoe
Frequent Visitor
2 years ago

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 isntance
Warehouse_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!
 
--Troekoe
For the average you can do the same:
Measure = AVERAGE(Your_table[Sales])
 
 
 
 
 
  • MandoMongo's avatar
    MandoMongo
    Regular Visitor
    2 years ago

    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 TYPESales 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. 

    • Troekoe's avatar
      Troekoe
      Frequent Visitor
      2 years ago

      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 visual
       
      KR,
       
      --Troekoe
       
      • MandoMongo's avatar
        MandoMongo
        Regular Visitor
        2 years ago

        Thank you Troekoe, much appreciated explanations