Countifs then average in DAX

A

Acidkill

New Member
Joined
Feb 21, 2010
Messages
9
Hi everyone,

I'm currently transitioning to PowerBi and is in a bit of mess with converting excel formulas to DAX.
I am tryin to get the number times the store_number has appeared each day the get the average number of times it appeared for the month or week.

thank you in advance as this site as been my lifeline since I joined

here is my data sample: [TABLE="width: 80"]
<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"><tbody>[TR]
[TD="width: 107, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][TABLE="width: 234"]
<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"> <tbody>[TR]
[TD="width: 107, bgcolor: #BFD2E2"]Ticket_Number[/TD]
[TD="width: 59, bgcolor: #BFD2E2"]FullDate[/TD]
[TD="width: 147, bgcolor: #BFD2E2"]Issue_Store_Number[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583339[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]14167[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583380[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]13586[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583383[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]366[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583429[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]214[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583473[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]15777[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583492[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]19801[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583513[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]15311[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583514[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]9117[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583628[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]10310[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583662[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]12887[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583684[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]17994[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583742[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]76[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583759[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]13835
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583765[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]2846[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583782[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]13088[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583829[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]1182[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583831[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]8899[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2001583832[/TD]
[TD="bgcolor: transparent, align: right"]12/1/18[/TD]
[TD="bgcolor: transparent, align: right"]9260[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi AcidKill,

What is your definition of Average Per month and Per Week. Can you provide the excel formulas you were using?
 
Upvote 0
Hi Hashiru,

Thank you for replying.

it's average number of time the store number has appeared in a given time period like a week or a month. I first created pivot table for the data, then just did the below--Ticket count monthly average.

hope that helps

[TABLE="width: 418"]
<colgroup><col width="398" style="width: 299pt; mso-width-source: userset; mso-width-alt: 14555;"> <col width="158" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5778;"> <tbody>[TR]
[TD="width: 398, bgcolor: transparent"][/TD]
[TD="width: 158, bgcolor: transparent"]Feb
[/TD]
[/TR]
[TR]
[TD="bgcolor: gray"]Total Number of Stores calling ERC[/TD]
[TD="bgcolor: transparent, align: right"]13307[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Total Number of New Stores Calling (Open < 1 year)[/TD]
[TD="bgcolor: transparent, align: right"]617
[/TD]
[/TR]
[TR]
[TD="bgcolor: gray"]Total Number of Stores Calling at 40% more (Exception)[/TD]
[TD="bgcolor: transparent, align: right"]3404
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Total Number of New Stores calling at 40% more than avg.*[/TD]
[TD="bgcolor: transparent, align: right"]255[/TD]
[/TR]
[TR]
[TD="bgcolor: gray"]Percent of Exception Stores Calling ERC(out of all stores)[/TD]
[TD="bgcolor: transparent, align: right"]25.58%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ticket Count Monthly Average
[/TD]
[TD="bgcolor: transparent, align: right"]4.24
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Exception threshold[/TD]
[TD="bgcolor: transparent, align: right"]5.93[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
PowerPivot formula to calculate the number of active projects given start and end dates
Replies
4
Views
2K
Kamolga
K
N
  • Question Question
Need help creating an aging measure
Replies
1
Views
961
Ozeroth
O
T
  • Question Question
PowerPivot Filtering DAX Formula
Replies
4
Views
934
SOQLee
S
DRSteele
  • Question Question
Averages per Day
Replies
3
Views
1K
sergioMabres
sergioMabres
A
  • Question Question
DAX Max Month Measure
Replies
4
Views
3K
gazpage
G

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top