Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community,
I am having an issue with what should be a simple thing to do, calculating the difference between measures. Below is the table:
The "Account" field in rows is a calculated item from Calculation Group 1 and the "Time Calculation" is a calculated item from Calculation Group 2.
As you can see, the difference between the whole number values is calculating correctly but the difference between the decimal numbers and percentages is not calculating correctly. (Highlighted in yellow)
The three variances are calculated as follows:
vs QBR = [Act] - [QBR]
vs AOP = [Act] - [AOP]
vs LY = [Act] - LY]
The decimal numbers are taking "Account" divided "Volume".
For example, Net Sales in Calculation Group 1 (row 10 in the matrix)
Net Sales =
VAR NS =
CALCULATE (
SELECTEDMEASURE (),
'ACT/QBR/AOP'[Line Item] = "Net Sales"
)
VAR Volume =
CALCULATE (
SELECTEDMEASURE (),
'ACT/QBR/AOP'[Line Item] = "Volume"
)
RETURN
DIVIDE (
NS,
Volume,
0
)
All other decimal numbers are calculated the same way except for the first variable.
I am not sure what the issue is but hoping someone can assist with what should be an easy thing to do.
Thank you!
Hi,
I think the issue here is the calculation group precedence logic. Because of this this the calculations do work for whole numbers, but when it comes to decimals the order of operations causes issues. E.g. the percentage difference of MTD calculations is the higlighted one instead of the desired output. Basically I suspect you should change your calculation group precedence. It is a different story if you first calculate ebit and then aggregate it instead of first aggregating the data and then calculating ebit.
Here is an article by SQLBI discussing the topic:
https://www.sqlbi.com/articles/understanding-calculation-group-precedence/
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Hi @ValtteriN
Thank you for the response. Is there a standard order of precedence for calculation groups? For example, if I had several calculation groups:
Base measures - Precedence: 10
Ratios - Precedence: 9
Time Intelligence - Precedence: 8
Variances - Precedence: 7
etc, etc, etc
I have not been able to find any information on the standard order of operations when it comes to calculation groups. Do you know if there is any such principle/rule to follow?
@Anonymous hard to say but I'll give it a try for a quick win:
try switching the precedence of the calculation groups. Whatever has a higher number now, give it a lower number the other.
Check this article for best reference:
https://www.sqlbi.com/articles/understanding-calculation-group-precedence/
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |