Forum Discussion

gvanbuhler's avatar
gvanbuhler
Frequent Visitor
5 years ago
Solved

Matrix sub-totals are incorrect

I have a Matrix with multiple groupings and the subtotals are not working correctly.  The visual is filtered to two specific invoices so I can ensure that the totals are working properly.

From the Transaction Header Table:

 - Sales and Tax columns are numeric data types specific to the invoices filtered on

 - Write Off Amt is a measure from the allocation table where the transaction type is equal to 'Write Off'

 - Transaction Amount is a measure from the allocation table where there is a calculated field to get the absolute value of the transaction amount for the specific invoices.

 

I would expect that the NAICS Subtotal would be $3,183.68 NOT 1.4M

I feel like I am missing something simple here

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi gvanbuhler 

    I think this issue may be caused by your calculate logic in your measure. Maybe in your measure you use All, Allselected or AllExpected Function to filter the table. These function will remove some filters or keep some filters in your table. Subtotal don't have some parameter like GEO, SIC Code, NAICS. So these funtion will cause wrong result in sutotal. 

    I will give some advice to get correct subtotal.

    1. You can try to sum your origin measure by your table.

     

    SUMX([Table],[SALES Measure])

     

    Sometimes you can get correct subtotal by this way.

    2. Calculate the subtotal result by a new measure. Then use If and HASONEVALUE. 

    For example:

     

    Sample = 
IF(HASONEVALUE(Table[NAICS]),[SALES Measure],[New Measure with result you want])

     

    If this reply still couldn't help you solve your problem, please share a sample with me without sensitive data by your Onedrive for Business. I need to know your data model.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi gvanbuhler 

    I think this issue may be caused by your calculate logic in your measure. Maybe in your measure you use All, Allselected or AllExpected Function to filter the table. These function will remove some filters or keep some filters in your table. Subtotal don't have some parameter like GEO, SIC Code, NAICS. So these funtion will cause wrong result in sutotal. 

    I will give some advice to get correct subtotal.

    1. You can try to sum your origin measure by your table.

     

    SUMX([Table],[SALES Measure])

     

    Sometimes you can get correct subtotal by this way.

    2. Calculate the subtotal result by a new measure. Then use If and HASONEVALUE. 

    For example:

     

    Sample = 
IF(HASONEVALUE(Table[NAICS]),[SALES Measure],[New Measure with result you want])

     

    If this reply still couldn't help you solve your problem, please share a sample with me without sensitive data by your Onedrive for Business. I need to know your data model.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    17 days ago

    DAX fix for this: wrap the measure in

    IF(HASONEVALUE([Dimension]), [YourMeasure],

    CALCULATE([YourMeasure], REMOVEFILTERS([Dimension])))

    — forces the total row to evaluate without the row context filter. If the issue is deeper (non-additive ratios like margins at subtotal level), you need a separate total measure. Alternatively, custom visuals like Flexa Tables give per-level subtotal control from the visual config without touching DAX.

    Free trial on AppSource. https://appsource.microsoft.com/en-us/product/power-bi-visuals/devspearllc1670524393721.flexa_tables