CODEWITHSUNDEEP
powerbidax
AKP
AKP

Reputation: 99

How to calculate average count of items on powerbi

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.

Upvotes: 2

Views: 17863

Answers (2)

Dmitri Caragheaur
Dmitri Caragheaur

Reputation: 192

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.

enter image description here

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":

enter image description here

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.

Upvotes: 1

Strawberryshrub
Strawberryshrub

Reputation: 3399

Just right click on your fields pane > New Measure and add the following expression:

YourAverageMeasure = AVERAGE(Table1[Price])

The same goes for the count:

YourCountMeasure = Count(Table1[Price])

New depending on what you drag and dro into your table/chart (or filter) the measure gets calculated based on the data. Left table is Price and Name. Right table is just the average measure.

enter image description here

Upvotes: -1

Related Questions