Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
I'm relatively new to PowerBI and struggling to find an answer to the problem below. I'm looking for a Measure to calculate the last column (TOTAL TAX FOR PO), which would be the total of the SKU TAX column for each PO Number in the 1st Column.
Example: TOTAL TAX PO for PO Number 1001 = $3+$2+$4+$1+$0+$0=$10.
Note: "SKU TAX" is a SUMX Measure combining 2 columns from two different tables.
PO Number | SKU | Qty | SKU TAX | TOTAL TAX PO |
1001 | AAA | 3 | $3 | $10 |
1001 | BBB | 2 | $2 | $10 |
1001 | CCC | 4 | $4 | $10 |
1001 | DDD | 1 | $1 | $10 |
1001 | EEE | 2 | $0 | $10 |
1001 | FFF | 1 | $0 | $10 |
1002 | AAA | 2 | $2 | $11 |
1002 | BBB | 4 | $4 | $11 |
1002 | CCC | 5 | $5 | $11 |
I tried with Calculate with a filter on PO Number, but it gives me the same result as my SKU TAX Measure.
Any help would be greatly appreciated.
Thanks
Solved! Go to Solution.
@IB-Dee - Perhaps try something like:
TOTAL TAX PO Measure =
VAR __PO = MAX('Table'[PO Number])
RETURN
SUMX(FILTER(ALL('Table'),[PO Number] = __PO),[SKU TAX])
@IB-Dee , You can try all expect
calculate([SKU TAX], allexcept(Table,[PO Number]))
Hey @amitchandak
Thanks for taking the time to respond.
I also tried the formula, unfortunately, it returned the sum of tax for all the PO Number, not the sum of tax for each PO. Not sure if I did something wrong.
Thanks
@IB-Dee - Perhaps try something like:
TOTAL TAX PO Measure =
VAR __PO = MAX('Table'[PO Number])
RETURN
SUMX(FILTER(ALL('Table'),[PO Number] = __PO),[SKU TAX])
Hey @Greg_Deckler
Thanks for taking the time to respond, much appreciated.
I'll need to go back to try to understand how you got there with this formula but it worked!
Cheers
@IB-Dee - Sure, first line gets the current value of the PO Number column for the current row. The RETURN statement uses ALL to break out of the current context of the individual row and then you use FILTER to filter it back down to just the rows in the table where the PO Number matches the __PO variable value. Then you just sum up the column values.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |