Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have the following table visual with Month/Year and Member categories with Measures 1, 2 and 3. Measure 3 is the sum of Measures 1 and 2. I would like to have a second visual with Measure 4 that shows the Month - Year sum of Measure 3 as shown under Visual 2 below but have not been able to find a way to do this.
Visual 1 | |||||
Month - Year | Member | Measure 1 | Measure 2 | Measure 3 (M1+M2) | |
Jan 2020 | A | 5 | 4 | 9 | |
Jan 2020 | B | 3 | 2 | 5 | |
Jan 2020 | C | 7 | 1 | 8 | |
Feb 2020 | A | 2 | 3 | 5 | |
Feb 2020 | B | 1 | 1 | 2 | |
Feb 2020 | C | 4 | 3 | 7 |
Visual 2 | |
Month Year | Measure 4 (Month - Year sum of Measure 3) |
Jan 2020 | 22 |
Feb 2020 | 14 |
Appreciate the help.
Steve
Solved! Go to Solution.
@Anonymous , Try
sumx(Table,[measure 3]) or
sumx(values(Table[Month-Year]),[measure 3])
Hi @Anonymous,
Not sure what the measures are doing (or if I'm losing my mind) but on visual 2 could you not just have the same matrix but take out the member column.. they should aggregate to month year level then.
Otherwise, this may help
SUMX( VALUES( Table[Member] ), [measure 3] )
Let me know how you get on.
Kris
Hi @Anonymous ,
Would you please try to use the following measure:
measure4 = SUMX(SUMMARIZE('Table','Table'[Month-Year],"_measure3",[measure3]),[_measure3])
It will be nice if you can show us some sample data by onedrive for business.
Best Regards,
Dedmon Dai
@Anonymous , Try
sumx(Table,[measure 3]) or
sumx(values(Table[Month-Year]),[measure 3])
Hello,
I tried the SUMX statements and the result is a number that is much higher than the actual total of Measure 3 for a given Month - Year. The data I'm working with is from a Summarized table if that makes any difference.
Thanks,
Hi @Anonymous ,
Would you please try to use the following measure:
measure4 = SUMX(SUMMARIZE('Table','Table'[Month-Year],"_measure3",[measure3]),[_measure3])
It will be nice if you can show us some sample data by onedrive for business.
Best Regards,
Dedmon Dai
Hi @Anonymous,
Not sure what the measures are doing (or if I'm losing my mind) but on visual 2 could you not just have the same matrix but take out the member column.. they should aggregate to month year level then.
Otherwise, this may help
SUMX( VALUES( Table[Member] ), [measure 3] )
Let me know how you get on.
Kris
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |