Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |