Help with incorrect totals in table and card visual

[jan0204]

I understand that powerBI is weird when it comes to displaying total of a measure in table visual or card visual. I have tried a few different ways to work around (use of summarize, Hasonefilter) but I am not able to understand what I am doing wrong.

Table with Data (Invoice Table): Date, Item#, Supplier, Last Paid price, Qty, List Price

here is a list of measures I am working with:

Variance Price = List Price - Last Paid Price

Total Invoiced Qty = SUM(Invoice Table[Invoiced Quantity])

Total Variance = SUMX(VALUES(Invoice Table[Item #]), [Variance on Price4] * [Total Invoiced Qty])

I am using this last measure Total Variance in my table visual and card visual. The calculation in each total row is OK, however, the Total row is something I am not able to solve for. Any help will be appreciated. 

FYI - I do have filters for Year, Qtr and Month. the Total should be updated based on the value selected.

[Anonymous · Accepted solution]

Hi @jan0204 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new measure to calculate the values.

Variance Price = SUMX('Invoice Table', 'Invoice Table'[List Price]) - SUMX('Invoice Table', 'Invoice Table'[Last Paid price])

Total qty = SUMX('Invoice Table', 'Invoice Table'[Qty])

Total Variance = SUMX(VALUES('Invoice Table'[Item]), [Variance Price] * [Total qty])

3.Drag the measures into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[jan0204]

It works ok when only the suppliers are distinct. If you change to aggregate by supplier, it does not work.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Clearly show the problem there and also the expected result.

[jan0204]

Supplier	Total Variance
Suppl. 1	$1,085,857.60
Suppl. 2	($125,318.63)
Suppl. 3	$113,237.83
Suppl. 4	$26,013.91
Suppl. 5	$1,640.00
Suppl. 6	$205.50
Total Result in table and card visual	266,305
Expected total	$1,101,636.21

[Ashish_Mathur]

Share the download link of the PBI file.