How to calculate average count of items on powerbi

Question

I have a table of product from a client. I need to calculate the average total price of products as well as the average count of products I used this dax expression for an average total

Average Total of Price = CALCULATE(AVERAGE(wedding_data[Price]))

Here is the sample of the table:

    [Buyer], John,Francis, Charles, John, Francis
    [Wedding Date], 6/1/2019, 1/2/2019, 9/1/2019, 6/1/2019, 7/7/2019
    [Location], AK, NY, CO,AK, TX
    [Product_Name],iPad, Jewel, Books, Jewel, Shoe
    [Price], $5, $7.5, $6.12, $10, $21

Using the DAX above, one will obtain the average total price. Now I need to do the average count of product, with the right DAX,

However, I am not sure what expression to use for the average count for each product.

I am supposed to get a value that if I multiply by the number of items, I will give the total sum of item.

Your question is a bit unclear, can you please provide an example of the data set? — OscarLar
above statement does not represent average, it represent % of the product.. like 40% of the product are Jewel, 20% are ipad.. Not sure what do you mean by average count of each product.. — Praveen DA
I provided an answer with a measure, alternatively there can be a calculated column alternative, depends on your report. — Dmitri Caragheaur

Dmitri Caragheaur Dmitri Caragheaur · Accepted Answer · 2019-05-07T08:33:40

Try this Measure:

 ProductPercentage =

 VAR ProductCount =
     CALCULATE ( COUNTROWS ( wedding_data ) )
 VAR TotalRows =
     CALCULATE ( COUNTROWS ( ALL ( wedding_data ) ) )
 RETURN
     ProductCount / TotalRows

You have to have the row context as product. One column has to be your product name and the second the measure.

ALTERNATIVLY

You can use a calculated Column to then summarize it in a table:

Column =
VAR productName = Table1[ProdName]
VAR ProductCount =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER ( Table1, Table1[ProdName] = productName )
    )
RETURN
    ProductCount

And then include it in a table using summarization type "Average":

And from here you can proceed performing any kind of operatins with the columnn or measure data.

It all depends on your data structure and performance considerations.

How to calculate average count of items on powerbi

2 Answers