Anonymous

Not applicable

7 years ago

Solved

Grand total weighed average - Count

Hi PBI experts,

I have the following table:

Goal: Count the number of transactions for each Product ID and then calculate a weighted average based on sales

number of products = count(Tabla[Product ID])

I mean, instead of the sum of 2 + 4 + 4 = 10 what I need is (2 x 300 + 4 x 190 + 4x265) / 755 = 3,21

Thank you very much

Reuben

Need Help

smpa01

7 years ago

Anonymous excel replication can be done through M and not DAX

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGwjaBsJyDbFME0RBI2RhYGM51RmRYIpilC3hwoGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each Table.RowCount(_), type number}, {"Sum_1", each List.Sum([Column2]), type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", Int64.Type}, {"Sum_1", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Sum_1"}),
Sum_1 = #"Removed Other Columns"[Sum_1],
Value1 = List.Sum(Sum_1),
Custom1 = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", Int64.Type}, {"Sum_1", Int64.Type}}),
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"Count", "Sum_1"}),
#"Inserted Multiplication" = Table.AddColumn(#"Removed Other Columns1", "Multiplication", each [Count] * [Sum_1], Int64.Type),
#"Removed Other Columns2" = Table.SelectColumns(#"Inserted Multiplication",{"Multiplication"}),
Multiplication = #"Removed Other Columns2"[Multiplication],
Value3 = List.Sum(Multiplication),
Value2 = Value3/Value1,
Custom3 = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", Int64.Type}, {"Sum_1", Int64.Type}}),
#"Inserted Multiplication1" = Table.AddColumn(Custom3, "Multiplication", each [Sum_1] * [Count], Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Multiplication1", "Custom", each ""),
Custom2 = Table.InsertRows(#"Added Custom",Table.RowCount(#"Inserted Multiplication"),{[Column1="",Count="",Sum_1=Value1,Multiplication=Value3, Custom=Value2]}),
#"Added Index" = Table.AddIndexColumn(Custom2, "RowIndex", 1, 1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Index",{{"Custom", type number}})
in
#"Changed Type2"

Anonymous

7 years ago

Even though smpa01 gave you the M version, here's how to do it in DAX.

TotalSales = SUM('Table'[Sales])


ProductCount = COUNTA('Table'[ProductID])


WeightedAvg = 
VAR v_table =
    SUMMARIZE(
        'Table'
        ,'Table'[ProductID]
        ,"Sales" ,[TotalSales]
        ,"ProductCount" ,[ProductCount]
    )
VAR total_sales =
    CALCULATE(
        [TotalSales]
        ,ALLSELECTED('Table')
    )
VAR weighted_avg =
    DIVIDE(
        SUMX(
            v_table
            ,[Sales] * [ProductCount]
        )
        ,total_sales
    )
RETURN
weighted_avg

10 Replies

Anonymous

Not applicable

7 years ago

Even though smpa01 gave you the M version, here's how to do it in DAX.

TotalSales = SUM('Table'[Sales])


ProductCount = COUNTA('Table'[ProductID])


WeightedAvg = 
VAR v_table =
    SUMMARIZE(
        'Table'
        ,'Table'[ProductID]
        ,"Sales" ,[TotalSales]
        ,"ProductCount" ,[ProductCount]
    )
VAR total_sales =
    CALCULATE(
        [TotalSales]
        ,ALLSELECTED('Table')
    )
VAR weighted_avg =
    DIVIDE(
        SUMX(
            v_table
            ,[Sales] * [ProductCount]
        )
        ,total_sales
    )
RETURN
weighted_avg

smpa01
Community Champion
7 years ago
Anonymousgreat job !!!

amitchandak
Super User
7 years ago
Easy in 2,3 steps. Step 2 and 3 can be merged
column Calc Discount = Sales[Sales]*Sales[Discount %] measures All Cal Disc = CALCULATE(sum(Sales[Calc Discount]),ALLSELECTED()) Overall disc = [All Cal Disc]/CALCULATE(sum(Sales[Sales]),ALLSELECTED())
- Anonymous
  Not applicable
  7 years ago
  Hi amitchandak , thanks for your answer!
  
  I don´t see clear how to apply your tips in my example, if I follow your guideline I would do the following:
  
  number of products = calculate(count(Tabla[Product ID]);ALLSELECTED())
  but this give me the sum of all products...
  
  Im a bit lost.
  - amitchandak
    Super User
    7 years ago
    Create a new column (Column not measure)
    New Col = Totalsales* No of product
    then create a measure, you need two measures Grant total of New Col and TotalSales
    For that use
    
    CALCULATE(sum(New Col]),ALLSELECTED())/CALCULATE(sum(Total Sales),ALLSELECTED())
    
    please take care of tablename

smpa01

Community Champion

7 years ago

Anonymousplease correct me if I am wrong. As I undertsand are you hoping for PBI to show the total of a separate Weighted Average column to be 3.21?

I created measures as following and row based weighted average values are calculated correctly but the total does not yield to be 3.21 as on the Total row it evaluates the Wegh. Avg. formula as same as other rows and it yields to 10

Sum = SUM('Table (2)'[Column2])
Count = COUNT('Table (2)'[Column1])
All sum = CALCULATE(SUM('Table (2)'[Column2]),ALL('Table (2)'))
Wegh. Avg. = ([Sum]*[Count])/[All sum]

Anonymous

Not applicable

7 years ago

This is how I would do in excell, I think this way is more clear (given that my english is quite bad :D and I did not express myself well)

smpa01

Community Champion

7 years ago

Anonymous excel replication can be done through M and not DAX

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGwjaBsJyDbFME0RBI2RhYGM51RmRYIpilC3hwoGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each Table.RowCount(_), type number}, {"Sum_1", each List.Sum([Column2]), type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", Int64.Type}, {"Sum_1", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Sum_1"}),
Sum_1 = #"Removed Other Columns"[Sum_1],
Value1 = List.Sum(Sum_1),
Custom1 = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", Int64.Type}, {"Sum_1", Int64.Type}}),
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"Count", "Sum_1"}),
#"Inserted Multiplication" = Table.AddColumn(#"Removed Other Columns1", "Multiplication", each [Count] * [Sum_1], Int64.Type),
#"Removed Other Columns2" = Table.SelectColumns(#"Inserted Multiplication",{"Multiplication"}),
Multiplication = #"Removed Other Columns2"[Multiplication],
Value3 = List.Sum(Multiplication),
Value2 = Value3/Value1,
Custom3 = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", Int64.Type}, {"Sum_1", Int64.Type}}),
#"Inserted Multiplication1" = Table.AddColumn(Custom3, "Multiplication", each [Sum_1] * [Count], Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Multiplication1", "Custom", each ""),
Custom2 = Table.InsertRows(#"Added Custom",Table.RowCount(#"Inserted Multiplication"),{[Column1="",Count="",Sum_1=Value1,Multiplication=Value3, Custom=Value2]}),
#"Added Index" = Table.AddIndexColumn(Custom2, "RowIndex", 1, 1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Index",{{"Custom", type number}})
in
#"Changed Type2"

Forum Discussion

Grand total weighed average - Count

10 Replies

Related content

Weighed Average DAX Expression

Average Grand total instead of Sum in Matrix

Average Grand Total Only in Matrix

Plot Weighed Average

Calculate weighed average