Illustration of difference between order intake and forecast

[Orstenpowers]

Dear all,

Hello! I am very new here and after trying a lot of internet research, "try & error" I recognize that I need some kind of support from your end...hopefully I won't bore you.

The below screenshot illustrates some business figures, the budget and the OI figures for a certain time period.

(I do not know if it is important or not, in the basis Excel file there is one column containing all values, while another column defines whwther it is a budget value or a OI value).

Now I would like to illustrate the difference (in %) between OI and budget, but I don't get managed. So for the first row, I would like to see a value of -1.16%.

How to do so???

I am looking forward to your help that is highly appreciated...

BR,

Orstenpowers

[Zubair_Muhammad]

Hi @Orstenpowers

Try a MEASURE like

=
CALCULATE ( SUM ( Data[Values] ), Data[Budget or Order Income] = "OI" )
    / CALCULATE ( SUM ( Data[Values] ), Data[Budget or Order Income] = "Budget" )
    - 1

[Orstenpowers]

@Zubair_Muhammad

Great, it works! Thank you so much!

The result of the formula is shown twice!? How can I remove/ hide one of these two columns?

How can I change the value from -0.12 into -12%?

Thanks in advance for your (or others) help! 🙂

[Zubair_Muhammad]

@Orstenpowers

Just select your MEASURE from the FIELDS.

Then from the MODELLING TAB you can choose % formatting

[Orstenpowers]

So easy!? You just need to know how to do. Again, thanks!

Do you also have an idea how to remove/ hide the first "Deviation" column?

[Zubair_Muhammad]

HI @Orstenpowers

You can hide the first deviation column by dragging it with your Mouse from the corner.....Just like you do in EXCEL

[Orstenpowers]

Hello @Zubair_Muhammad,

Normally I know well about the Excel functionalities, but right now I feel a bit "stupid", but the only handling of this column looks as follows:

But if I choose "Ausschließen" ("exclude"), two columns "Budget AmountEUR" and "Deviation (rel)" are removed...but only the marked column should disappear...

[Zubair_Muhammad]

Hi @Orstenpowers

My apologies... I didn't explain it well enough...

Actually from the  border of every  column you can simply rezise it... Just by hodling your mousebutton and dragging it

Please send me your file...if you are unable to do so

[Orstenpowers]

Hi @Zubair_Muhammad,

now I made it. However, by doing so I just set the width of this column to 0, right? In consequence I see that the rows are higher now...

Isn't it possible to suppress a column like you can do in Excel?

[Zubair_Muhammad]

HI @Orstenpowers

Please turn off the WORD WRAP property for both "VALUES" and "COLUMN HEADERS"

Hopefully this will fix it.. Please See the image below

[Orstenpowers]

[Orstenpowers]

@Zubair_Muhammad!

Thank you very much for your huge contribution! I never had made it without your support!

[Orstenpowers]

@Zubair_Muhammad:

So far, so good. 🙂

However, to get it completed, I now need to add two further columns.

Before explaining which ones, some words about the current composition of the table.

The report currently is filtered by Month (January) and Year (2018).

The additional columns should be as follows:

1st column: OI AmountEUR of Year 2017 (even better would be a reference to the other year value – 1, so that I can always produce a comparison to prev. year, depending on the selection of the “newer” year)

2nd column: Deviation (rel) between OI AmountEUR of Year 2017 and OI AmountEUR of Year 2018 (second column at the moment)

Can you help me to get this setup accordingly?

Filters are set as follows at the moment:

[Zubair_Muhammad]

HI @Orstenpowers

Try this MEASURE

OI AmountEUR of Previous Year =
CALCULATE (
    SUM ( Data[OI AmountEUR] ),
    FILTER ( ALL ( Data[Year] ), Data[Year] = SELECTEDVALUE ( Data[Year] ) - 1 )
)

[Orstenpowers]

Hello @Zubair_Muhammad,

Your proposal seems to work, but something is strange.

I am looking at six different geographic regions with two different tables. One, including your proposal, the other one is an own creation.

4 out of six regions show the same results, that's good. But for two regions, the results are different. With your proposal, some values obviously are not considered for these two regions. I cannot find the difference in the setup that causes this...

Something else:

To my original Excel file I added a column with the ISO 3166 country codes. Now I would like to upload this info into my Power BI, but I don't get it done...can you help me?