Percentage of Target Diff Calculation

DAX DAX Calculations
1

Hi,

I have a Table using the Financial Reporting w/Power BI as a base to insert the rows and I would like to have a Column to give me the Diff between Actual Sales and Budgeted Sales as a percentage.
I have been able to work out the CurrentItem Variable line but the rest of the lines I cannot get the formula to work the same

This is the DAX I have written down to line 6 works fine I just cannot get the last part to work for the individual lines -
image

This is my table visualisation
image

Any help would be greatly apprecaited - I love you content by the way has helped me immensly

Cheers

2

Why don’t you just keep it a lot simpler and use the column you’ve already calculated here.

All you should need is

DIVIDE( Diff, Target, 0 )

That’s it.

See how you go with this.

Sam

3

Hi,
Thanks for the response

The Diff colum is calculated as follows:
image

So changing to the following gave me an error “The Expression refers to multiple columns. Multiple columns cannot be converte to a scalar value”

% Diff Bgt vs Actual MTD Parts Analysis =
VAR CurrentItem = SELECTEDVALUE( 'PartsAnalysis2'[Items (Normalized)] )
RETURN
SWITCH( TRUE(),
CurrentItem = "Total Case IH GP", DIVIDE( [Diff BGT vs Actual MTD Parts], [Total Rev Parts BGT], 0 ),
DIVIDE( [Diff BGT vs Actual MTD Parts], [Total Rev Parts BGT], 0 ),
FILTER( 'FinancialCategories', 'FinancialCategories'[Comparison] = CurrentItem ) )

I’m sure I’m missing something simple?

Cheers

4

Ok I might be wrong here but aren’t you just way over thinking this.

You literally already have the actual, target and diff results.

So to work out the % all you need to do is use those measures like I mentioned above in a simple DIVIDE statement.

Like this.

DIVIDE( Diff, Target, 0 )

Isn’t this all you need to get what you want?

Thanks

5

Yes that is what I am after,

If i do the simple formula -
image

I get the following error when i drop it into the visulisation
image

I am trying to use the Financial Statment method to get the formatting i want and I am wondering if that is what is makeing it difficult?

Thanks again for looking at this one

6

Hi,

Ok I think I can see what is happening, if i remove the Formats from the numbers in the DIff measure I can then get the simple measure as you put above to work.

Do you know if there is a way to format the numbers using the Measure Modelling with a comma and negative numbers with brackets? eg: 4,000 or (4,000) I can do with a symbol but would rather not have the symbol cluttering the report

Thanks for the help

Cheers

7

Ok great,

No I don’t believe there is a way to do this right at the moment unfortunately.

Sam

1 Like