Forum Discussion
Summin a calculated weighted average in a Matrix Column subtotal
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
Hello cschuchert,
Now I understand what You need.
The aswer is pretty simple after you discover, and you will be making the same fix a lot of times =).
The DAX is doing the same measure you create for the totals, It consider the total line as another row in the table, that way the results in error for you that want the totals of lines.
For this treatment you will need to tell DAX that if you filter the column "Product_Type" you want the sum of row.
You will use IF plus HASONEFILTER or ISFILTERED, you chose one or another.
Like:
Measure = IF ( ISFILTERED ( ColumnName[Product_Type] ) ; Sum(TableName[Category] ; SUMX ( 'Table', Table[Forecast_Value] * Table[DaysInMth]) / SUMX ( 'Table', Table[DaysInMth] ) )
Cheers,
The Matrix in my screenshots is only filtered by the Quarter. I also have multiple slicers on the page for the Forecast week, Division, ProductType and Quarter used in another Matrix on the same page. The Matrix using the meaure is to get a CWA for each Quarter; all filters work on both Matrixes.
If I filter this to one Division the Total is correct but if all Divisions are selected the Total is the Average.
What exactly is Sum(TableName[Category] doing in the measure example? I understand an IF statement needs a True/False but all I really want is for this measure to Sum the values used in my original measure.
v-cherch-msftMicrosoft Employee
Hi 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)