Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yasmin
Frequent Visitor

Sumif and averageif

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!

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-cherch-msft
Employee
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.