Power BI totals not adding up correctly

[nleuck]

Hello Everyone,

I'm having some trouble with Power BI totals in a table not adding up to the correct amount. I've tried it two different ways and got the same result. The total I'm getting from Power BI is 10,155 but when you add up the numbers it should be 11,211.

The first total is a measure TOTALYTD(DISTINCTCOUNT(table[LedgerID]), datetable[date])

The second total is just column LedgerID where I do a Count (Distinct) on the value and I filter on Year 2021.

Any help would be greatly appreciated.

Thanks!

[JBY]

It appears the Distinct Count is a problem. The disaggregated values don't add up to the total. 

I have a similar challenge here. When I sum the male/female percentages, the return is more than 100%.

[v-jayw-msft]

Hi @nleuck ，

The second total is also a calculated value. If you want the total to show the specific result, you may use measure to do that. Show some sample data and we might be able to help.

Best Regards,

Jay

[nleuck]

@m3tr01d 

I get that. But take a calculator and those numbers up they don't add up to the total. The visual is misleading.

[aj1973]

Hi @nleuck 

can you share your Pbix file, or a Sample of it?

[m3tr01d]

Hi @nleuck , 
I understand but I'm just saying that the result is as expected from the DAX you have. 

Then, you can either remove the grand total from the table if you don't need it or change the logic of the DAX.

If you want to change the grand total so the numbers are added up. You can do it like this :

YTD_Measure = TOTALYTD(DISTINCTCOUNT(table[LedgerID]), datetable[date])

YTD_Visual = 
SUMX( 
    VALUES( Table[MonthNameShort] ),
    [YTD_Measure]
)

Hope this helps.

[m3tr01d]

@nleuck It's exactly as @aj1973 is saying. LedgerId is present in multiple months and the distinct count is only counting once LedgerId. The grand total will always be smaller.

[aj1973]

Hi @nleuck 

Is 11,211 the count totaling duplicate values?

Is there a filter applied somewhere?

You are not providing much details, can you share your file please?

[nleuck]

@aj1973 

If you add those totals up in the screenshot they don't add up to 10,155. When I add those numbers together I get 11,211.