Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
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
Why not just use:
AVERAGE = AVERAGE(Table9[VDA AMOUNT])
See attached PBIX and you want Table9, Page 4
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())
Mother Table:
MONTH | GROUP | CHG_CODE | VDA AMOUNT | BKG_NUM | CON_NUM | VDA NUM |
November-18 | CCU | WASH | 9.75 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | SUR | 9.75 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | SOR | 0 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | LAS | 0.05 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | ISPS | 3.25 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | ICOM | -10 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | FRT | 300 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | EQP | 26 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
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
MONTH | GROUP | CHG_CODE | VDA AMOUNT | BKG_NUM | CON_NUM | VDA NUM |
November-18 | CCU | WASH | 9.75 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | SUR | 9.75 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | SOR | 0 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | LAS | 0.05 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | ISPS | 3.25 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | ICOM | -10 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | FRT | 300 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
November-18 | CCU | EQP | 26 | SHLPK18001152 | AMFU3221483 | AGIN1819000129HQ |
How can get measure for this case
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |