Grand total weighed average - Count

[Reuben]

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

[smpa01 · Accepted solution]

@Reuben  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"

[Drewdel · Accepted solution]

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]

@Drewdelgreat job !!!

[smpa01]

@Reubenplease 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]

[Reuben]

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

[smpa01 · Accepted solution]

@Reuben  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"

[Reuben]

thanks @smpa01  for your answer! At this stage understanding M is too complicate for me, so diving into the forum I came out with this solution:

I summarize my table grouping by Product ID and calculating the sum of sales and the count of number of products.

Then I calculate the average using the SUMX expression.

Weig. Av = divide(sumx(summarize(tabla;Tabla[Product ID];"Sales";sum(Tabla[Sales]);"# Prod";count(Tabla[Product ID]));[Sales]*[# Prod]);sum(Tabla[Sales]))

Here is the result

thanks @smpa01 & @amitchandak for your help again, all your contributions make me allow me to learn PBI a little more.

Thanks!!

[amitchandak]

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())

[Reuben]

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]

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

[Reuben]

But I cannot create a new column because my table has just two columns

Product ID: which is the name of the SKU (alphanumeric)

Sales: the number of units sold

Product ID for each transaction will allways be 1

What I need to calculate is how many transactions are for each SKU (count).

I think there is something than I am not understanding 😞