Forum Discussion

kalif's avatar
kalif
Icon for Helper I rankHelper 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]) an...
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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Why not just use:

 

AVERAGE = AVERAGE(Table9[VDA AMOUNT])

See attached PBIX and you want Table9, Page 4

kalif's avatar
kalif
Icon for Helper I rankHelper 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())