Is the problem with my Data Tables?

[bigck2]

Hello,

I am new to DAX and Power Pivot. I have been trying to create some PowerPivot data models and reports. One of the things I have liked about PowerPivot is the ability to make asymetric pivot tables with Sets based on columns. I have been able to make a nice looking report to show Budgets vs Actuals:


[TABLE="width: 556"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Column Labels[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2014[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Budget[/TD]
[TD]Actuals[/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Total Revenues[/TD]
[TD]Total Revenues[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[TD="align: right"]$14,127,324.81[/TD]
[TD="align: right"]$13,614,776.31[/TD]
[/TR]
[TR]
[TD]Ft. Lauderdale[/TD]
[TD="align: right"]$7,532,754.78[/TD]
[TD="align: right"]$7,223,285.95[/TD]
[/TR]
[TR]
[TD]XXXXX[/TD]
[TD="align: right"]$7,532,754.78[/TD]
[TD="align: right"]$7,223,285.95[/TD]
[/TR]
[TR]
[TD]Orlando[/TD]
[TD="align: right"]$6,594,570.03[/TD]
[TD="align: right"]$6,391,490.36[/TD]
[/TR]
</tbody>[/TABLE]



I have been trying to make DAX calculated fields to now come up with a Variance in $$ and as %.

However, I have not been able to figure this out. I have been resorting to copying the Pivot Table into another sheet and entering in excel formulas to get the results.

I'm wondering if I can't figure this out because the problem is actually in my Table layout and Data Model.


I currently only have two tables in my data model. The main one I called Data looks like this:


[TABLE="width: 783"]
<colgroup><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Month Year[/TD]
[TD]Year[/TD]
[TD]Month2[/TD]
[TD]Month3[/TD]
[TD]Type[/TD]
[TD]Property[/TD]
[TD]Retail Rental Revenue[/TD]
[TD]Total Rental Inc w/Adj[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]2014[/TD]
[TD]Mar[/TD]
[TD="align: right"]3[/TD]
[TD]Actuals[/TD]
[TD]XXX[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]736821.62[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]2015[/TD]
[TD]Mar[/TD]
[TD="align: right"]3[/TD]
[TD]Budget[/TD]
[TD]XXX[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]730791.61
[/TD]
[/TR]
</tbody>[/TABLE]


There are some more columns but what I am trying to show is that I have separate rows for Actuals and rows for Budgets in the same table.

I then have another table that has a relationship based on the Property Code, and there are various Property attributes in that table.

I am wondering based on some white papers I have read online if I want to be able to get Variance as a calculated field I would need to separate the data into two tables, one for budget and one for actuals???

I am really wanting to build a solution that will be able to compute Budget Variances and Same Store Sales.

Please let me know if you have any suggestions.

Thanks,

Chris

 

[ImkeF]

No problem at all using Actuals and Budgets in one table. Create measures like this:

Act:=CALCULATE(SUM([Amount]); 'YourTable'[Type]="Actuals")
Bud:=CALCULATE(SUM([Amount]); 'YourTable'[Type]="Budget")
DiffBud:=[Act]-[Bud]
DiffBud%:=DIVIDE([DiffBud];[Bud])

(you might need to replace ; with , depending on your language settings)

 

[bigck2]

Hey ImkeF,

Thanks for your reply. I have made some progress today.

Thank you for reassuring me this is possible without separating into multiple tables.

-Chris