If multiplication

Anything related to PowerPivot and DAX Formuale
Posts: 5
Joined: Sun Nov 22, 2020 9:14 pm

If multiplication

Postby yoshi_anko » Wed Nov 25, 2020 2:29 pm

Hello, I want to make inventory value based on the sales value basis, stock QNTY x sales unit price (excluding tax).

1. One Inventory transactions (location, Item_Number, quantity, Extended_Cost)
2. One look up table with product Item details including Item_Number, unit sales price, and tax information whether taxable or free.

I made related on the Inventory query to pull unit sales price and tax information.

Then tried to make a Measures, but it did not work, number became gigantic number. I only want to write a formula
---if tax column is tax, then unit price x 0.909090 (or divided 1.1 times 1) x quantity, if tax free, quantity x unit sales price.

What I did in Measure
Inventory QNTY =SUM(Inventory[Transform File.Quantity])
Sales Unit Price =SUM(Inventory[Sales Unit])
Inventory with sales value basis =CALCULATE(Inventory[Inventory QTY]*[Sales Unit Price]) <- this needs to be conditional formula , if tax column is "tax", quantity x unit sales price x 0.9090990, if not quantity x unit price.

Would you mind rectifying this?


Excel2.png (21.49 KiB) Viewed 215 times
Excel1.png (30.9 KiB) Viewed 215 times

User avatar
Jason Cockington
Posts: 6
Joined: Thu Jan 16, 2020 8:46 am

Re: If multiplication

Postby Jason Cockington » Thu Nov 26, 2020 3:43 pm

Hi Yoshi,
A tricky little problem you have there.
I believe this formula should resolve your challenge:

VAR RawCalc = RELATED(ItemMaster[Selling Price]) * Inventory[QTY] // this gives us our Extended Amount
VAR TaxDisc = if(Inventory[Tax]="Tax",RawCalc/1.1) // If Tax has been applied, then provide the value before Tax
VAR Result = if(TaxDisc=0,RawCalc,TaxDisc) // if Tax hasn't been applied, give the Extended Amount, otherwise give the Tax Discount value
) // Iterate over the Inventory table to determine the appropriate Extended Amount value, then sum the result.

Given that we are going to have to step through the Inventory table one row a time to perform the calculation, there is no need to manifest your RELATED(ItemMaster[Selling Price]) as a calculated column in your Inventory table. Instead we can bring this information into the formula as we step through the table.

Please note this isn't a particularly efficient formula, because it has to step through the entire Inventory table to perform the row by row evaluation of the calculation. This is necessary because the TAX information is being applied at the Inventory table.

My question for you then:
Can the Tax be applied to the ItemMaster table by product, or is it being applied by site, and hence sometimes a product could be sold with tax and other times it isn't?
If it can be applied by Product, it would be much more efficient to have the TAX column in the ItemMaster table, and then step through the smaller lookup table, with a RELATED(Inventory[Qty]) value being brought in from the Inventory table.

As a general rule, any time you have to use an iterating function like SUMX() you want it to step through the smallest possible table, so if possible, you should avoid running a SUMX() over your data table.
Jason Cockington is Self Service BI Trainer and Consultant with Excelerator BI

Posts: 5
Joined: Sun Nov 22, 2020 9:14 pm

Re: If multiplication

Postby yoshi_anko » Thu Nov 26, 2020 10:59 pm

Thanks Jason for your detailed answer, it worked!! Brilliant!

Yes, tax is applied to the ItemMaster table by product, not by site. In that case how do I need to modify? Sorry I'm still a very novice Power Pivot user.
Tax column is in ItemMaster table.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests