Forum Discussion
Getting ridiculously wrong subtotals in matrix visual
I am trying to build a visual with three rows and no columns. When I add a couple of values that I am bringing in from a data set, the subtotals are either correct for one or the other, not both:
Above, Net Shipped Units subtotal correctly
Above, Net Shipped Dollars subtotal correctly.
It appears whichever column I add latest has the correct subtotal. Both of these metrics are coming straight from my fact table - there are no calculations done on them from inside of Power BI, aside from the option to sum them within the matrix visual.
However, I next try to add a custom metric, and now I get a number that is even more wrong than 0:
The calculation for the custom metric is pretty basic:
Net Ship GM% = DIVIDE(SUM(Demand[Ship $]) - SUM(Demand[COGS]),sum(Demand[Net Ship $]))
What am I doing that is causing these subtotals to aggregate incorrectly?
Thank you
10 Replies
As highlighted in yellow, returning the 0 is weird. Please check your data type and create again if it still has the issue. Essentially, it will return the total sum of all Net Ship in AUTO.
I try to reproduce your scenario using the follow sample data.
Then, I create below measure, and get expected result without issue.Net Ship GM% = DIVIDE(SUM(Test[Net Ship $]),SUM(Test[Net ship U]))
For you issue, what are “Ship” and “COGS” columns in the custom metric? If there is a logical error, could you please share the sample date or screenshot for further analysis?
Best Regards,
AngeliaI have rebuilt this visual with a new pbix file and found I could replicate it by adding a filter on Net Ship U is greater than 0. I had put this filter in my original matrix because without it I am seeing all products in my Product Dimension table, whether or not they had any sales associated with them. v-huizhn-msft, when you put a filter on your Net Ship U in the Visual's filters does that allow you to replicate my strange results?
I should have added that these rows without values only show up when I added a custom metric to the table:
Item Rank NC$ = RANKX(ALL('Product'[Item Number and Description]),Demand[Total NC$],,DESC)This is a metric that ranks the items in the matrix by Net Contribution Dollars.
