topic Re: Tax report sometimes doubles taxes in DAX Commands and Tips

Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 15:26:10 GMT

I have created a report that computes taxes on taxable sales. Taxes on some transactions are double what they shouId be. I have found this occurs when a transaction contains two of the same Products. Here is an example looking at the table view

Two Rents are billed on Ref_No E101765 for $75 each.

Here is that same transaction on my report view.

The total of Taxable Sales is $150 and the Tax Rate is 7%. so the Sales Tax should be $10.50.

The two transactions above this example have the same problem while the remaining transactions do not.

Below is a link to the report.

Thanks,

Bud & Zeke

https://1drv.ms/u/c/4f83aa2c44fa9c7f/EQ2aewebvDJCq7bK0ct9l0IBU7u6W5XYXubE1qUm_uPfbg?e=9x23az

Re: Tax report sometimes doubles taxes

SachinNandanwar — Tue, 30 Jul 2024 16:42:02 GMT

This ?

TaxableSales = SUMX( DISTINCT( FILTER( History, RELATED(Tax[Tax-No Tax]) = "T" ) ), (History[TOTAL_PRICE]) )

You would have to write a similar measure for Quantity as well to move out the duplicates

Re: Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 17:00:43 GMT

SachinNandanwar

Thanks for looking at this problem. However we are not having a problem with the Taxable Sales but rather with the calculation of the Sales Tax. Even though there are two of the same product, they were correctly billed. The trouble is the Sales Tax are twice as much as they should be. This only occrurs when there are two billings of the same product on one Ref_NO.

Re: Tax report sometimes doubles taxes

SachinNandanwar — Tue, 30 Jul 2024 17:04:07 GMT

If you look the screenshot I posted the Sales Tax comes to 10.50% instead of 21% compared to yours.

Re: Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 17:14:38 GMT

Yes the sales tax is correct but the Taxable Sales are $75 and they should be $150.

Thanks

Re: Tax report sometimes doubles taxes

SachinNandanwar — Tue, 30 Jul 2024 17:44:04 GMT

You can change the measure that calculates the Sales Tax to this

Sales Tax = Var _TaxableSales= SUMX( DISTINCT( FILTER( History, RELATED(Tax[Tax-No Tax]) = "T" ) ), (History[TOTAL_PRICE]) ) Return SUMX( FILTER( History, RELATED(Tax[Tax County]) = History[Tax County] && RELATED(Tax[Tax Type]) = History[Tax Type] ), RELATED(Tax[Tax Rate]) * _TaxableSales /100 )

and keeping your original measure for Taxable Sales as is

Re: Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 17:42:17 GMT

SachinNandanwar

Thanks so much for your help. Much appreciated

Re: Tax report sometimes doubles taxes

SachinNandanwar — Tue, 30 Jul 2024 17:53:57 GMT

Hi BudMan512

Just realised that the total for "Sales Tax" is incorrect.

My gut feeling is that DAX is evaluating the total sales tax row and individual sales tax row in different contexts.Working on a solution to fix it.

Re: Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 18:08:28 GMT

Oh thanks. I was so exited that the rows were working that I missed the total. Thanks for picking that up.

Re: Tax report sometimes doubles taxes

SachinNandanwar — Tue, 30 Jul 2024 18:46:32 GMT

BudMan512

Please try this

Sales Tax = SUMX( SUMMARIZE(History, History[Tax County], History[Tax Type], History[Quantity], Tax[Tax Rate], "TotalPrice", SUMX( ( FILTER( History, RELATED(Tax[Tax-No Tax]) = "T" && RELATED(Tax[Tax County]) = History[Tax County] && RELATED(Tax[Tax Type]) = History[Tax Type] ) ), [TOTAL_PRICE] ) ), [Tax Rate] * [TotalPrice] / 100 )

Re: Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 19:31:34 GMT

Perfect, thank you

Re: Tax report sometimes doubles taxes

BudMan512 — Tue, 30 Jul 2024 19:32:38 GMT

Thanks