Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Subtracting two measures not working

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:

 

gordo_c_123_0-1656444403276.png

 

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!

3 REPLIES 3
ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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?

SpartaBI
Community Champion
Community Champion

@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/


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors