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 by Item Type = 
SUMX(
    VALUES(Warehouse_and_Retail_Sales[ITEM TYPE]), [Total Retail Sales]
)

 

 

Both of these return a table with each item and a figure - but the figures are the same for each item per table. I though it should be an averaged figure per type and then a total figure per type. It seems to just give a final figure underneath the table whcih is either averaged or totalled. Is there a way to achieve what I'm looking for? With averaged per item type and total per item type

I'm new. Trying to calculate Total Sum by Product Type. 

I've managed it by creating a group in power query, but that resides in a separate table which can't be affected by date slicers etc. 

 

So I'm  trying to create a calculated measure to do this. But I keep getting the error can't convert type Text to True/False. This is the code below. 

 

Sales Amount by Items =
CALCULATE(
    WarehouseSales_Calcs[Total Retail Sales], FILTER(Warehouse_and_Retail_Sales, Warehouse_and_Retail_Sales[ITEM TYPE])
)

 

 

So I've tried another way... but its the same error of course. Can anyone help?

 

Sales Amount by Items =
CALCULATE(
    [Total Retail Sales],  
    FILTER(Warehouse_and_Retail_Sales, Warehouse_and_Retail_Sales[ITEM TYPE])
    )
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
     

6 Replies

  • Troekoe's avatar
    Troekoe
    Frequent Visitor
    2 years ago

    Yo!
    I am assuming the calculation you are trying to do looks similar to this:

     

    Product typeSUM
    A10
    B5
    C20
    D100

     

     

    The nice thing about Power BI is that it calculates a group for you in visuals automatically.
    If you were to create a measure = SUM(Your_Table[Sales]) and use this measure with your column: Warehouse_and_Retail_Sales[ITEM TYPE] in a visual.

    It will automatically show you the sum for each group.
    Look at this example with the data and measure i just told you about:

     

    Hope this helps!

     

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