I have a calculated column on my Item table that looks like this. I know, a SWITCH would have probably been prettier, but this works. Each item in the Item table ends up with the correct rate assigned.
Rate = IF (OR('Item'[Class]="a",'Item'[Class]="b"), 1.07,
IF('Item'[Class]="c",3.4,
IF('Item'[Class]="d",1.57,
IF('Item'[Class]="e",3.15,
0)))
)
I have a table visual that shows a set of items from the item table (Item Number, Description, Rate) all of which is correct.
The table also shows qty onhand from my Inventory table, which is also correct. The visual works as expected based upon the date and item filters that are configured for the page. In other words, the qty is correct for each item, and the total qty is correct for items shown.
The original issue was: when trying to calculate the total tax = (qty * rate) for each item, the rows were correct, but the grand total was wrong. I read various articles on non-additive measures, and I think that I understand why. The calculation was working for the row context, but the total was showing me the average rate for all items * the total qty, where what I actually wanted was the sum of the qty * rate for only the items shown on the page.
In my example, the rate is a fixed amount per qty, not a percentage.
I got to the answer I wanted, but I'm not sure if this is ideal, and would be interested to know what a more elegant solution might be?
My solution was to first create a measure that reprensents the rate for each item per above.
I'm using AVERAGEX here, although in the row context, this doesn't matter because there is only one row per item.
Rate New = AVERAGEX('Item','Item'[Rate])
I then get the correct tax per item, and the correct total tax for all items showin in the visual as follows.
Tax New = SUMX( Inventory,Inventory[Qty] * 'Item'[Rate New] )
- Why did I need to create the Rate New measure as opposed to just referencing the Rate calculated column?
PowerBI would not let me just reference the calculated column.
- Why can't I just use AVERAGEX('Item','Item'[Rate]) instead of referencing the other measure in Tax New?
OR Why can't I create a VAR and return AVERAGEX('Item','Item'[Rate]), then use the VAR in Tax New?
If I try either of these, it does not weight the total based upon qty of each item.
