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.
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 displays the CWA for each category perfectly.
But when I add Column Subtotals in the Matrix Format Properties, the CWA is an average. I want the Column Subtotal to sum the CWA for the categories.
Is there an otpion I cannot see available in the Format Properties to do this or do I need to rewrite the expression in such a way to get what is needed? If so how do I get the sum of the CWA instead of an Average of the CWA? If I have to display this in 2 different Matrixes with 2 different formulas I will but to me that is not an optimal solution.
Thanks,
Solved! Go to Solution.
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
Can you send the result Matrix before and after the adition of Subtotals,
That way I can Help you.
See screenshots. Let me know if you only wanted the data.
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.
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:
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
Hi @cschuchert
It seems you haven't shown the data.
Regards,
Cherie
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) |
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |