Forum Discussion
Solved
Summin a calculated weighted average in a Matrix Column subtotal
I have a simple calculated weighted average formua which works exactly as expected: Totals = SUMX('Table', Table[Forecast_Value]*Table[DaysInMth])/SUMX('Table', Table[DaysInMth]) The Matrix disp...
Hi cschuchert
You may refer to below measure.
Totals = SUMX ( 'TableA', TableA[Forecast_Value] * TableA[DaysInMth] ) / SUMX ( 'TableA', TableA[DaysInMth] )Subtotal = VAR a = SUMMARIZE ( TableA, TableA[Division], "b", [Totals] ) RETURN SUMX ( a, [b] )Regards,
Cherie
You are a genius!! I saw your post where it looked like I hadn't sent the data-I'm not sure what happened to my post with the excel data but it's there now.
I cannot thank you enough!! I'll have to research SUMMARIZE so I understand how to apply it to other visualizations.
Regards,
Carolyn
v-cherch-msft
Microsoft Employee
Microsoft EmployeeHi cschuchert
You may have a look at below posts. If you need further help, please share some simplified data sample.
How to Get Your Question Answered Quickly
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Regards,
Cherie
Thank you-I did find that post yesterday and can get the first part of the Measure I'm stuck on the false portion though. Here is the data for this app:
DaysInMth Division Forecast_Date Forecast_Period Forecast_Type Forecast_Value Gas FA Gas FA Mcfe FA NGL FA Oil FAVolume Mcfe NGL Oil Product_Type 31 2 LH 8/29/2018 0:00 10/1/2018 0:00 Production 110698 0 0 114730 0 0 114730 110698 0 0 04 Mcfe 31 2 LH 8/29/2018 0:00 10/1/2018 0:00 Production 110617 110617 114649 0 0 0 114649 0 0 0 01 Mcf 31 2 LH 8/29/2018 0:00 10/1/2018 0:00 Production 13 0 0 0 0 13 13 0 0 13 02 Oil(Bbl) 31 2 LH 8/29/2018 0:00 10/1/2018 0:00 Production 0 0 0 0 0 0 0 0 03 NGL(Bbl) 31 3 MC 8/29/2018 0:00 10/1/2018 0:00 Production 0 0 3810 0 0 0 3810 0 0 0 01 Mcf 31 3 MC 8/29/2018 0:00 10/1/2018 0:00 Production 0 0 0 0 0 315 315 0 0 0 02 Oil(Bbl) 31 3 MC 8/29/2018 0:00 10/1/2018 0:00 Production 0 0 0 0 571 0 571 0 0 0 03 NGL(Bbl) 31 3 MC 8/29/2018 0:00 10/1/2018 0:00 Production 0 0 0 9126 0 0 9126 0 0 0 04 Mcfe 31 3 NL 8/29/2018 0:00 10/1/2018 0:00 Production 261763 0 0 274524 0 0 274524 261763 0 0 04 Mcfe 31 3 NL 8/29/2018 0:00 10/1/2018 0:00 Production 197717 197717 209010 0 0 0 209010 0 0 0 01 Mcf 31 3 NL 8/29/2018 0:00 10/1/2018 0:00 Production 8932 0 0 0 9124 0 9124 0 8932 0 03 NGL(Bbl) 31 3 NL 8/29/2018 0:00 10/1/2018 0:00 Production 1743 0 0 0 0 1795 1795 0 0 1743 02 Oil(Bbl) 31 4 SP 8/29/2018 0:00 10/1/2018 0:00 Production 1919610 0 0 1907547 0 0 1907547 1919610 0 0 04 Mcfe 31 4 SP 8/29/2018 0:00 10/1/2018 0:00 Production 1301493 1301493 1311507 0 0 0 1311507 0 0 0 01 Mcf 31 4 SP 8/29/2018 0:00 10/1/2018 0:00 Production 93438 0 0 0 89740 0 89740 0 93438 0 03 NGL(Bbl) 31 4 SP 8/29/2018 0:00 10/1/2018 0:00 Production 9581 0 0 0 0 9600 9600 0 0 9581 02 Oil(Bbl) 30 2 LH 8/29/2018 0:00 11/1/2018 0:00 Production 108272 0 0 112682 0 0 112682 108272 0 0 04 Mcfe 30 2 LH 8/29/2018 0:00 11/1/2018 0:00 Production 108194 108194 112604 0 0 0 112604 0 0 0 01 Mcf 30 2 LH 8/29/2018 0:00 11/1/2018 0:00 Production 13 0 0 0 0 13 13 0 0 13 02 Oil(Bbl) 30 2 LH 8/29/2018 0:00 11/1/2018 0:00 Production 0 0 0 0 0 0 0 0 03 NGL(Bbl) 30 3 MC 8/29/2018 0:00 11/1/2018 0:00 Production 0 0 3722 0 0 0 3722 0 0 0 01 Mcf 30 3 MC 8/29/2018 0:00 11/1/2018 0:00 Production 0 0 0 0 0 306 306 0 0 0 02 Oil(Bbl) 30 3 MC 8/29/2018 0:00 11/1/2018 0:00 Production 0 0 0 0 556 0 556 0 0 0 03 NGL(Bbl) 30 3 MC 8/29/2018 0:00 11/1/2018 0:00 Production 0 0 0 8893 0 0 8893 0 0 0 04 Mcfe 30 3 NL 8/29/2018 0:00 11/1/2018 0:00 Production 252014 0 0 254158 0 0 254158 252014 0 0 04 Mcfe 30 3 NL 8/29/2018 0:00 11/1/2018 0:00 Production 190230 190230 193186 0 0 0 193186 0 0 0 01 Mcf 30 3 NL 8/29/2018 0:00 11/1/2018 0:00 Production 8595 0 0 0 8432 0 8432 0 8595 0 03 NGL(Bbl) 30 3 NL 8/29/2018 0:00 11/1/2018 0:00 Production 1703 0 0 0 0 1730 1730 0 0 1703 02 Oil(Bbl) 30 4 SP 8/29/2018 0:00 11/1/2018 0:00 Production 1934494 0 0 2004568 0 0 2004568 1934494 0 0 04 Mcfe 30 4 SP 8/29/2018 0:00 11/1/2018 0:00 Production 1314863 1314863 1392232 0 0 0 1392232 0 0 0 01 Mcf 30 4 SP 8/29/2018 0:00 11/1/2018 0:00 Production 93984 0 0 0 92522 0 92522 0 93984 0 03 NGL(Bbl) 30 4 SP 8/29/2018 0:00 11/1/2018 0:00 Production 9287 0 0 0 0 9534 9534 0 0 9287 02 Oil(Bbl) 31 2 LH 8/29/2018 0:00 12/1/2018 0:00 Production 105821 0 0 110610 0 0 110610 105821 0 0 04 Mcfe 31 2 LH 8/29/2018 0:00 12/1/2018 0:00 Production 105746 105746 110536 0 0 0 110536 0 0 0 01 Mcf 31 2 LH 8/29/2018 0:00 12/1/2018 0:00 Production 12 0 0 0 0 12 12 0 0 12 02 Oil(Bbl) 31 2 LH 8/29/2018 0:00 12/1/2018 0:00 Production 0 0 0 0 0 0 0 0 03 NGL(Bbl) 31 3 MC 8/29/2018 0:00 12/1/2018 0:00 Production 0 0 3634 0 0 0 3634 0 0 0 01 Mcf 31 3 MC 8/29/2018 0:00 12/1/2018 0:00 Production 0 0 0 0 0 296 296 0 0 0 02 Oil(Bbl) 31 3 MC 8/29/2018 0:00 12/1/2018 0:00 Production 0 0 0 0 542 0 542 0 0 0 03 NGL(Bbl) 31 3 MC 8/29/2018 0:00 12/1/2018 0:00 Production 0 0 0 8658 0 0 8658 0 0 0 04 Mcfe 31 3 NL 8/29/2018 0:00 12/1/2018 0:00 Production 243757 0 0 239295 0 0 239295 243757 0 0 04 Mcfe 31 3 NL 8/29/2018 0:00 12/1/2018 0:00 Production 183902 183902 181671 0 0 0 181671 0 0 0 01 Mcf 31 3 NL 8/29/2018 0:00 12/1/2018 0:00 Production 8310 0 0 0 7930 0 7930 0 8310 0 03 NGL(Bbl) 31 3 NL 8/29/2018 0:00 12/1/2018 0:00 Production 1666 0 0 0 0 1674 1674 0 0 1666 02 Oil(Bbl) 31 4 SP 8/29/2018 0:00 12/1/2018 0:00 Production 1898958 0 0 1978827 0 0 1978827 1898958 0 0 04 Mcfe 31 4 SP 8/29/2018 0:00 12/1/2018 0:00 Production 1290941 1290941 1370625 0 0 0 1370625 0 0 0 01 Mcf 31 4 SP 8/29/2018 0:00 12/1/2018 0:00 Production 92553 0 0 0 92162 0 92162 0 92553 0 03 NGL(Bbl) 31 4 SP 8/29/2018 0:00 12/1/2018 0:00 Production 8783 0 0 0 0 9205 9205 0 0 8783 02 Oil(Bbl) - v-cherch-msft
- v-cherch-msft
Hi cschuchert
You may refer to below measure.
Totals = SUMX ( 'TableA', TableA[Forecast_Value] * TableA[DaysInMth] ) / SUMX ( 'TableA', TableA[DaysInMth] )Subtotal = VAR a = SUMMARIZE ( TableA, TableA[Division], "b", [Totals] ) RETURN SUMX ( a, [b] )Regards,
Cherie
You are a genius!! I saw your post where it looked like I hadn't sent the data-I'm not sure what happened to my post with the excel data but it's there now.
I cannot thank you enough!! I'll have to research SUMMARIZE so I understand how to apply it to other visualizations.
Regards,
Carolyn