PowerPivot Tbl Not doing what it should-Summing Sub Totals not records

[kimallen]

Stumped on this one and have to admit a little annoyed as this is easy as pie in a regular Excel Pivot table but can't figure it out for the life of me with Power Pivot.

I basically have a field I calcualted in Dax that figures out the usage for lines of credit. If I have a $10,000 line of credit and used $5,000 then my usage would be 50%, 7,500 75%, etc.

The formula worked find in DAX using DIVIDE, but when I put it into a pivot table the table wants to sum the total usage example

Region
North .50
South .25
East. .30
West. .20

Total would be 135 as it's counting the colums and instead should be giving me a grand total of usage based on the total Line of credit and the total used.

Not sure if I'm making sense or not and have spent the better part of the past two days trying to figure this out and can't. Really wish it was as easy to make formulas right in a pivot table in power pivot as it is regular excel.

Thanks in advance

 

[kimallen]

Should show you my formulas

Regular Pivot Table in Excel that works fine
[TABLE="width: 218"]
<COLGROUP><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10605" width=290><TBODY>[TR]
[TD="width: 290, bgcolor: transparent"]=(OSRound)' /CMTRound' )[/TD]
[/TR]
</TBODY>[/TABLE]



PowerPivot where it doens't work fine. This is me treating it as a measure and putting it directly into the PTbl

=([OSRound]/[CMTRound])

This is the error I get

"Calculation error in measure 'TBL_Qry3YearClose'[Measure 2]: The value for 'OSRound' cannot be determined. Either 'OSRound' doesn't exist, or there is no current row for a column named 'OSRound'."



In a desperate attempt to do a workaround this is the DAX calculation I did directly into my table hoping it would work and it works on a record basis but once I try and put it into the pivot table it fails

=DIVIDE([OSRound],[CMTRound])

Driving me crazy as dividing between two columns is common in reporting and others have had to have had this issue too but don't see anything on google or here on work arounds.

Thanks again

 

[kimallen]

No need to reply. I figured it out. In order for me to do the calculation I needed to creat two seperate measures. 1 a measure of Sum of CMTs Round and 1 for OS Round. After that I divided those two measures agaisnt one another and success. Do have to say it's much easier in regular Excel but I'm getting the hang of it slowly but surely.

Thanks anyway.