Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Stephane Gelinas]

Hello all,

I am a DAX beginner and need help your help!   I am currently developing something in Excel (PowerPivot) and my DAX formula that calculates a variance between budget and actual doesn’t work correctly when there is no value for an account, either at the “budget” or the “actuals”.

I have a data table that contains the budget and expenses for condominium units.

It is possible that for an account there is a value budgeted but we have no actuals (no line, e.g. a null value), and it is possible that there is an actual expense, but nothing was budgeted (again, no line).

The data table (which I simplified for this example) looks like this :

Year	Scenario	Account No	Account Description	Condo Unit	Amount (Montant)
2019	Actual	5100	Electricity	6835	100
2019	Budget	5100	Electricity	6835	110
2019	Actual	5200	Maintenance	6835	50
2019	Budget	5300	Various expenses	6835	25

 

I would like to calculate a variance, based on applied filter (e.g., slice the report per Condo Unit).

The formula works when there is data in both actual and budget  (e.g., the first two lines in the example), but doesn’t work,  or actually works occasionally, when either the actual or the budget has no value (example: the last 2 lines).

This is the formula I used (it’s in French so slightly different from the simplified example) :

Ecart:=CALCULATE(SUMX(DataTable, DataTable [Montant]), DataTable [Scénario]=”Budget”)-CALCULATE(SUMX(DataTable, DataTable [Montant]), DataTable [Scénario]=”Réel”)

 

Example of the report :

			Budget	Réel
No Compte	Catégorie	Sous-catégorie	Montant	Montant	Ecart
1000	Cotisation régulière	Cotisation régulière	9,974	30,843	-20,869
			9,974	30,843	-20,869
5100	Assurances	Assurances	12,035	13,633	-1,598
5210	Entretien du bâtiment	Ventilation		4,397	-4,397
5225	Entretien du bâtiment	Portes/fenêtres et serrures		1,209	-1,209
5240	Entretien du bâtiment	Luminaires		25	-25
5260	Entretien du bâtiment	Articles saisonniers		126	-126
5280	Entretien du bâtiment	Divers	2,000
5310	Déneigement	Stationnements	794	830	-36
5320	Déneigement	Entrées avant	641	670	-29
5330	Déneigement	Escaliers	1,321	1,381	-60
5340	Déneigement	Cours commune	263	275	-12
5350	Déneigement	Déglaçage / Sel		18	-18
5410	Paysagement	Entretien	2,200
5420	Paysagement	Réparations / remplacement	200
5500	Électricité	Électricité	1,300	1,358	-58
5610	Honoraires professionnels	Juridiques		379	-379
5700	Interphone	Interphone	600	626	-26
5710	Frais bancaires	Frais bancaires	360	319	41
5720	Frais de réunions et d’assemblées	Location de salle	300	264	36
5730	Dépenses diverses	Dépenses diverses	500	1	499
5910	Contingence	Contingence	1,500
5925	Transfert entre fonds	Transfert entre fonds	-14,041	20,682	-34,723
			9,974	46,196	-36,223

 

 Any idea on how to fix this?    I tried splitting the query to have one table for the Budget and an other for the Actuals and linking both to a “calendar” table, but ended up having the same number repeating across lines…

 Any help would be greatly appreciated!

 Thanks!

[Aubrey McKillop]

Stephane, 

You could wrap your formula with an IF State checking to make sure that budget and Actuals both have values.  If they do use the calculate if they do not apply another value such as 0

Aubrey

——————————
Aubrey McKillop
Director of IT
Moncton NB
——————————
——————————————-

[Stephane Gelinas]

Hello Aubrey, 

Thanks for your reply.  Unfortunately it seems to yield the same results…  

This is what I have tried : 

Ecart:=if(isblank(CALCULATE(SUMX(DataTable,DataTable[Montant]),DataTable[Scénario]=”Budget”)),0,CALCULATE(SUMX(DataTable,DataTable[Montant]),DataTable[Scénario]=”Budget”))-if(isblank(CALCULATE(SUMX(DataTable,DataTable[Montant]),DataTable[Scénario]=”Réel”)),0,CALCULATE(SUMX(DataTable,DataTable[Montant]),DataTable[Scénario]=”Réel”))

Best regards, 

Stephane

——————————————-

[Paul Cooper]

The key is in the problem with many to many relationships.
The budget table cannot have a relationship with the actuals table as the account key will have many in both tables. That is no one-to-many relationship.
I solved this by creating a table with just the account numbers.  You may have that already, if so, that step is done.
Then use TREATAS to get the Budget Table to TREAT this account number (in the budget table) AS the Account Number in the Accounts table.
Alternatively use the TREATAS with SUMMARIZE to achieve this.
Excel will not recognise the TREATAS in intellisense as you type the formula but it actually does work.

For detail see Matt Allington’s post here.
Virtual Filters Using TREATAS

Exceleratorbi		remove preview
Virtual Filters Using TREATAS I have been aware of the TREATAS function for a very long time. I have tried to understand how it works a few times but could never really work it out. I think part of the problem is the function name TREATAS is not very descriptive. View this on Exceleratorbi >

——————————
Paul Cooper
Director
Rinstrum Pty Ltd
Acacia RIdge
0418744345
——————————
——————————————-

[Stephane Gelinas]

Hello,

Thank you for the reply,  this seems promising, I will explore the option.

Perhaps another way of solving this would be to add a bridge table, I just looked it up, it seems to be another way to fix the problems that can occur with many to many relationships.  Perhaps I could split my information in two tables (one for budget, the other one for actuals), and use the accounts table as a bridge table… I’ll look into this as well.

UPDATE

I split the information in two tables and used the account table as a bridge between the actuals and the budget table.  It seems to work correctly!

My DAX formula now looks something like this  (translated as the formula uses some french terms) :

=SUMX(‘Budget’,Budget[Budget])-SUMX(‘Actuals’,’Actuals'[Actuals])

——————————————-