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
PrizzC15
Frequent Visitor

Sum and divide DOW based on month filtering

Hi all,

I need helps as below:

PrizzC15_0-1671515114927.png

 

I had tried to upload the pbix file, but fail.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @PrizzC15 ,

I created some data:

vyangliumsft_0-1671607062576.png

Here are the steps you can follow:

1. Create measure.

sum divde =
var _selectYear=SELECTEDVALUE('Table'[Year])
var _selectmonthname=SELECTEDVALUE('Table'[Month Name])
var _month=MAXX(FILTER(ALL('Table'),'Table'[Year]=_selectYear&&'Table'[Month Name]=_selectmonthname),[Month])
var _count=COUNTX(FILTER(ALL('Table'),
'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[Day]=MAX('Table'[Day])),[Day])
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Day]=MAX('Table'[Day])&&'Table'[Month]=_month),[Value])
return
DIVIDE(_sum,_count)

2. Result:

vyangliumsft_1-1671607062578.png

 

Best Regards,

Liu Yang

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

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @PrizzC15 ,

I created some data:

vyangliumsft_0-1671607062576.png

Here are the steps you can follow:

1. Create measure.

sum divde =
var _selectYear=SELECTEDVALUE('Table'[Year])
var _selectmonthname=SELECTEDVALUE('Table'[Month Name])
var _month=MAXX(FILTER(ALL('Table'),'Table'[Year]=_selectYear&&'Table'[Month Name]=_selectmonthname),[Month])
var _count=COUNTX(FILTER(ALL('Table'),
'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[Day]=MAX('Table'[Day])),[Day])
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Day]=MAX('Table'[Day])&&'Table'[Month]=_month),[Value])
return
DIVIDE(_sum,_count)

2. Result:

vyangliumsft_1-1671607062578.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi @v-yangliu-msft 

 

Im trying to create the measure that you provided but I get something as below:

PrizzC15_0-1675144199161.png

 

Measure =
var _selectYear=SELECTEDVALUE(ODS_Labor_Hours[Year])
var _selectmonthname=SELECTEDVALUE(ODS_Labor_Hours[Month Name])
var _month=MAXX(FILTER(ALL(ODS_Labor_Hours),ODS_Labor_Hours[Year]=_selectYear&&ODS_Labor_Hours[Month Name]=_selectmonthname),[Month Name])
var _count=COUNTX(FILTER(ALL(ODS_Labor_Hours),
ODS_Labor_Hours[Year]=MAX(ODS_Labor_Hours[Year])&&ODS_Labor_Hours[Month Name]=MAX(ODS_Labor_Hours[Month Name])&&ODS_Labor_Hours[Day Name]=MAX(ODS_Labor_Hours[Day Name])),[Day Name])
var _sum=SUMX(FILTER(ALL(ODS_Labor_Hours),ODS_Labor_Hours[Day Name]=MAX(ODS_Labor_Hours[Day Name])&&ODS_Labor_Hours[Month Name]=_month),[VALUE])
return
DIVIDE(_sum,_count)
Mahesh0016
Super User
Super User

Mahesh0016_0-1671521505330.png

Mahesh0016_1-1671521564838.png

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

FarhanAhmed
Community Champion
Community Champion

Create a relationship between your table using composite key "Month-Day"

Then try to create DAX

 

_Avg Day Sales = CALCULATE( Divide(SUM([Sales),SUM(Table 2 [Count]),BLANK()))







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




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.