Sumif and averageif

[yasmin]

Good Morning! I have been using PowerBi recently and I still have difficulty performing some operations that I believe are basic.

I am looking for which function to use in Power BI, which has the same result as SUM.IF (S) and AVERAGE.IF in excel so that it is not necessary to write the condition, but to read the cell condition of a column of the corresponding row.

Just explaining what I'm trying to do I have the following tables:
1) PRODUCTS: where I have the registration of each product (one per line), they appear only once with their respective information.
2) Sales: where I have the following columns STORE, TICKET, DATE, PRODUCT, REVENUE, QTD. It is basically a sales history with the sale of each product per store per ticket.
3) Inventory stores: Has the columns STORE, PRODUCT, QTD. Shows how much in stock I have for each product per store.

I am trying to create two measures:
(a) one calculating the average sales of a product in the last four months
b) a measure that sums the total inventory of the product (adding all stores)
In this case, the key collumn would be the PRODUCT column

Thank you!

[v-cherch-msft · Accepted solution]

Hi @yasmin

You may try to use CALCULATE Function or AVERAGEX Function. Here is the document for you. If it is not your case, please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.

https://powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif/

https://community.powerbi.com/t5/Desktop/Average-of-last-6-months/td-p/128738

Regards,

Cherie