Forum Discussion

kalif's avatar
kalif
Helper I
7 years ago
Solved

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

 
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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. create a calendar date table and connect it to your table, then add year and month from the calendar date table to the slicer,

     

    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

4 Replies

    • kalif's avatar
      kalif
      Helper I
      7 years ago

      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

    • kalif's avatar
      kalif
      Helper I
      7 years ago

      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())

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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. create a calendar date table and connect it to your table, then add year and month from the calendar date table to the slicer,

     

    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