Totals of type by Customer in columns using Powerpivot DAX

[ribberflavenous]

I have tried combinations of Calculate, Filter, Countax and I just can't see to wrap my head around this. I am new to the PowerPivot and the DAX, so I have not hit the AHA point yet. Probably easier to show than explain what I am looking to do.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Customer[/TD]
[TD]Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]Widget Inc[/TD]
[TD]Quote[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]ABC Co[/TD]
[TD]Quote[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]Widget Inc[/TD]
[TD]Sale[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]ABC Co[/TD]
[TD]Quote[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]ABC Co[/TD]
[TD]Sale[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]Widget Inc[/TD]
[TD]Sale[/TD]
[TD]$100[/TD]
[/TR]
</tbody>[/TABLE]

Using the above Data, trying to come up with DAX code that will give me the below Pivot table view. I want to see if the store is closing many quotes on a summary level and then drill down if something looks wrong to see what customer is not being closed to a sale.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]# Quotes[/TD]
[TD]$ Quotes[/TD]
[TD]# Sales[/TD]
[TD]$ Orders[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]2[/TD]
[TD]$200[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-Widget Inc[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-ABC Co[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]1[/TD]
[TD]$200[/TD]
[TD]2[/TD]
[TD]$300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]- Widget Inc[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]- ABC Co[/TD]
[TD]1[/TD]
[TD]$200[/TD]
[TD]1[/TD]
[TD]$200[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[theBardd]

Why not just add two calculated columns in the PP model, Quotes and Sales, with formulae of

=IF([Type]="Quote",[Amount],0)
=IF([Type]="Sale",[Amount],0)

and then pivot over these two columns.