Complicated Calculation - Cal 2

Hi all ,

I would like to request help with the final calculation ( Cal 2) which is division 2 values Total holidays and Cal1 ( Total Holiday/ Avg). I need to achieve results like this in the column marked red.

So far I have this in PBI file (below) but the last column doesn’t show the correct values for specific locations ( like in the screenshot from excel )

Would be grateful for your support.
Holidays_17 locations_forum.pbix (175.3 KB)
Holiday.details.forum1.csv (119.3 KB)
Employee details_ forum1.csv (88.2 KB)

Iwona

Hi @Iwona,

If you are going to use the slicer on the page and still want to consider Avg total of all Locations, make the below adjustment.

Avg total 2 = 
CALCULATE( [avg],
    ALL(Location[Location])
)

As for Calc2, that’s just another rounding issue, if this is key for your calculations please look into that for all dependant measures.

Calc2 = 
DIVIDE( 
    ROUND( [Total _hol], 0 ),
    ROUND( [Calc1], 0 ),
    0
)

.
Returns this result

I hope this is helpful

Hi @Melissa

Many thanks for the above explanation.

Would it be ok to kindly request help for “cal3” and “cal 4”.
cal 3 = Total avg company * total employees (per location)
cal4 = Total holiday / cal 3

PBI and Excel have different values in these two columns, meaning PBI shows incorrect figures, and don’t know where I am wrong. :frowning:

Expected figures :

and PBI with wrong figures

Holidays_17 locations_forum.pbix (175.4 KB)

Excel calculations are here:
Holiday.final forum1 .xlsx|attachment (18.9 KB)

Grateful as always for your expertise and support.

Iwona

Hi @Iwona,

Well please share your XLSX with expected outcome, that would be helpful.

Thanks.

@Melissa

The file with the expected outcome is already uploaded at the bottom of the previous post and I hope all is acceptable there.
Please find it below as well.

Holiday.final forum1 .xlsx|attachment (18.9 KB)

Extremely grateful,

Iwona

1 Like

Hi @Iwona,

Review these.
I’ve added some calculations to your xlsx because some figures where hardcoded.

Iwona sample file.xlsx (19.6 KB)
Iwona sample file.pbix (175.6 KB)

I hope this is helpful

Hello @Iwona ,

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION.

Thank you

Hello @Iwona

Just following up if the response above helps you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

We’ve noticed that no response was received from you on the post above. If there won’t be any activity in the next few days, we’ll tag this post as Solved.

Hi @Iwona,

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!