Percent difference between two measures

DAX DAX Calculations
1

Hola EDNA - needed some help to make sure I’m calculating this correctly. I need to know the Percent delta between two numbers. measuring against a base line and how we perform against it.

  1. Base = AVERAGEX( XT ,XT[SF])

  2. A = DIVIDE([ActH],[EarnH])*[SF])

  3. % = CALCULATE( DIVIDE([Base], [A],0)-1

the goal is to stay below the base, the lower the better.

Secondly if the above measures look correct. I don’t want the AU3 to appear if I have no data in measures (Base & A), please see pic.

image
image297×955 54.1 KB

Thank you for your help in advance,

Frankee.

2

Managed to figure out the empty column challenge by following this tutorial:

Computing totals by summing visible values in DAX

% = 
SUMX(
    VALUES('Calendar'[WE]),
    CALCULATE(
        IF ( 
            DIVIDE([Base],[A],0)<>0,
            CALCULATE(
                    DIVIDE([Base],[A],0)-1

                    )
            )

        )       
)

pic below of result

image

Only question still need help with is, the first…is % difference between two measures calculating correctly?

Thank you in advance,

Frankee

3

Hi @Frankee ,

If its giving the right number then its good .

Thanks

4

some of the numbers are wrong. I’m expecting what’s in Green column. the yellow rows match closely …but not exact…if I increase the decimals places…they’re not identical.

image

again, thank you in advance.

Frankee

5

Hi,

Can you provide pbix so we can look into that solved your problem quickly .

Thanks

6

Hi @Frankee , I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

7

Here’s the PBI file…just took me a while to scrub it a bit.
DM-XT.pbix (198.4 KB)

thank you for the help,

Frankee

1 Like
8

Thanks for providing the pbix file.

9

Hi @Frankee ,

Sorry for delay in reply first i think is solved your problem and i am able to get your desired result as you may see in the below screen shot:
image

I am attaching the pbix which conation the solution :
DM-XT.pbix (198.4 KB)

Let me know if you have any question.

Thanks

10

Hell @Anurag , no worries. looks like this was the solution. appreciate the help. typical, I complicated what I was doing.

Thank you,

Frankee