Forum Discussion

benjames's avatar
benjames
Frequent Visitor
2 years ago

DAX Calculation Won't Display Result

I have a table with 4 columns, Store #, Header ID, Transaction Date, Amount.  With this DAX "1" and "2" end up with values based on the sum; these values can be filtered by Store # or Date.  "3" doesn't have any transactions, this should be a value between the division of "1" and "2".  I'm at a loss at how to change this so that "3" will show a value as well as filter as required.

 

To test, I did make a fake $0 transaction for "3" on another sheet and then merged it into the transaction table.  This then gives me a percentage but it's all numbers being divided and it won't filter by Store #.

 

Any help would be greatly appreciated! 

 
Format Totals =
VAR Val = Sum('office posted_transaction_item'[amount])
 
VAR Val3 = Divide
(CALCULATE(sum('posted_transaction_item'[amount]),
Filter(ALL('posted_transaction_item'), 'office posted_transaction_item'[Header ID] = "2")),
   
CALCULATE(sum('posted_transaction_item'[amount]),
Filter(ALL('posted_transaction_item'), 'office posted_transaction_item'[Header ID] = "1")))

 

Return
Switch

 

(Selectedvalue ('posted_transaction_item'[Header ID]),

 

"1", Format (Val, "#,##0;(#,##0)"),
"2", Format (Val, "#,##0;(#,##0)"),
"3", Format (Val3, "Percent"))

1 Reply