Counting Transaction Status

[Anonymous]

The goal is to the create a table that will count the number of transactions with regards to the current status and total. Example if you order a pizza, the status of the pizza will go from: new > build in progress > baking > boxing > complete. There are 5 status' to the transaction and the price does not change. Problem is my current measure can count the status' per transaction but it cannot distinguish what is complete vs outstanding (ex: pizza1 [current count output = 1 Complete / 4 Outstanding] vs. pizza1 should = 5 Complete because the last status is Complete ). How do we count the number of actions + total sale considering the current status and price? How would you approach this scenario? 

Below are sample data to give context (table 1 = sample data / table 2 = desired output)

(Table 1 = Sample Data)

transaction_id	transaction_date	status	price
pizza1	Wed. Nov 25, 2020	New	$5.00
pizza1	Fri. Nov 27, 2020	In Progress	$5.00
pizza1	Mon. Nov 30, 2020	Baking	$5.00
pizza1	Tue. Dec 1, 2020	Boxing	$5.00
pizza1	Fri. Dec 4, 2020	Complete	$5.00
pizza2	Thu. Nov 26, 2020	New	$12.00
pizza3	Wed. Dec 2, 2020	New	$10.00
pizza3	Wed. Dec 2, 2020	In Progress	$10.00
pizza3	Thu. Dec 3, 2020	Complete	$10.00
pizza4	Mon. Nov 30, 2020	New	$5.00
pizza4	Fri. Dec 4, 2020	Canceled	$5.00

(Table 2 = Desired Output)

transaction_id	transaction_count	status_count	current_status	total_price	completed_price	outstanding_price
pizza1	1	5	Complete	$5.00	$5.00	$0.00
pizza2	1	1	New	$12.00	$0.00	$12.00
pizza3	1	3	Complete	$10.00	$10.00	$0.00
pizza4	1	2	Delete	$5.00	$0.00	$5.00

Any advice is greatly appreicated

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Please download this PBIX file with data and code to produce this table

There are 2 things to mention.

I've deliberately left out the Transaction_Count column as this always appears to be 1.  Each transaction will be unique?  I can add this in if needed.

For Cancelled orders your final table shows the Current_Status as Delete.  I've left it as Canceled but again, can change this if needed.

Regards

Phil