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
kalif
Helper I
Helper I

How to create measure count the Con_num while selected Month

Find below table & below measure using Average & Count, But While select the Month in Filter below measure is not refreshed

 

 

AVERAGE = SUM(VDASUM1[VDA AMOUNT]) / COUNT(VDASUM1[AID_CON_NUM]) and 

CON_NUM = COUNT(VDASUM1[AID_CON_NUM])

 

S. No

CON_NUM

BKG_NUM

VDA AMOUNT

MONTH

1

RFCU2361667

SHLPK18000635

1557.76

Nov-18

2

RFCU2317031

SHLPK18000635

1538.16

Nov-18

3

RFSU2001852

SHLPK18000635

1538.16

Nov-18

4

RFCU2330199

SHLPK18000835

818.9

Nov-18

5

NLLU2017678

SHLPK18000798

775.2

Nov-18

6

GATU1311080

SHLPK18001145

729.63

Dec-18

7

RFCU2362956

SHLPK18001145

729.63

Dec-18

8

RFSU2003979

SHLPK18001145

729.63

Dec-18

9

TGHU3686289

SHLPK18001145

729.63

Dec-18

10

NLLU2016563

SHLPK18001046

716.5

Dec-18

11

NLLU2016590

SHLPK18001046

716.5

Dec-18

12

RFSU2006005

SHLPK18001046

716.5

Dec-18

13

RFSU2005225

SHLPK18000985

714

Oct-18

14

RFCU2318824

SHLPK18000784

710.6

Oct-18

15

RFCU2319292

SHLPK18000784

710.6

Oct-18

16

GATU1301230

SHLPK18000788

710.6

Oct-18

17

TGHU0131688

SHLPK18000788

710.6

Oct-18

18

NLLU2016856

SHLPK18001044

675.01

Oct-18

19

CRSU1219920

SHLPK18001152

647.44

Oct-18

20

RFCU2340094

SHLPK18000681

646

Oct-18

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

Hi @kalif

When i test with your dataset, i can make the month slicer work for my measure.

1. add "month" in the slicer

2.png

 

2. create a calendar date table and connect it to your table, then add year and month from the calendar date table to the slicer,

3.png

 

Measures

sum = SUM(Sheet2[VDA AMOUNT])

count = COUNT(Sheet2[CON_NUM])

average = [sum]/[count]

average2 = AVERAGE(Sheet2[VDA AMOUNT])

 

If you have any problem please let me know

 

 

Best Regards

Maggie

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @kalif

When i test with your dataset, i can make the month slicer work for my measure.

1. add "month" in the slicer

2.png

 

2. create a calendar date table and connect it to your table, then add year and month from the calendar date table to the slicer,

3.png

 

Measures

sum = SUM(Sheet2[VDA AMOUNT])

count = COUNT(Sheet2[CON_NUM])

average = [sum]/[count]

average2 = AVERAGE(Sheet2[VDA AMOUNT])

 

If you have any problem please let me know

 

 

Best Regards

Maggie

Greg_Deckler
Super User
Super User

Why not just use:

 

AVERAGE = AVERAGE(Table9[VDA AMOUNT])

See attached PBIX and you want Table9, Page 4


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Dear Greg, Thanks for your below measure, its working fine, need your assist, this table  is child table 2 , get date from SUMMARIZE from Mother Table  & CALCULATETABLE from child table 1 (above table =CALCULATETABLE('VDASUM', VDASUM[AID_CON_NUM] <> BLANK())

 
 
How to can set the measure & filters set from Mother Table

 

Mother Table: 

 

MONTHGROUPCHG_CODEVDA AMOUNTBKG_NUMCON_NUMVDA NUM
November-18CCUWASH9.75SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUSUR9.75SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUSOR0SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCULAS0.05SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUISPS3.25SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUICOM-10SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUFRT300SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUEQP26SHLPK18001152AMFU3221483AGIN1819000129HQ

 

Child table 1 = VDASUM = SUMMARIZE(VDA, VDA[AID_BKG_ID], VDA[AID_BKG_NUM], VDA[AID_CON_NUM],VDA[Month], "VDA AMOUNT", SUM(VDA[AID_F_AMT]))

 

Child table 2 = =CALCULATETABLE('VDASUM', VDASUM[AID_CON_NUM] <> BLANK())

Thanks Mr. Greg_Deckler, Below measure working fine, but am using Table SUMMARIZE from another table, filers set from mother table, find below for mother Table format

 

MONTHGROUPCHG_CODEVDA AMOUNTBKG_NUMCON_NUMVDA NUM
November-18CCUWASH9.75SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUSUR9.75SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUSOR0SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCULAS0.05SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUISPS3.25SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUICOM-10SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUFRT300SHLPK18001152AMFU3221483AGIN1819000129HQ
November-18CCUEQP26SHLPK18001152AMFU3221483AGIN1819000129HQ

 

How can get measure for this case

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.