Complicated Calculation - Cal 2

DAX DAX Calculations
1

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.

image
image828×504 29.3 KB

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 )

image
image837×487 19.5 KB

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

2

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

image
image785×420 16.9 KB

I hope this is helpful

3

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 :

image
image913×474 16.8 KB

and PBI with wrong figures

image
image727×422 20.6 KB

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

4

Hi @Iwona,

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

Thanks.

5

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

image
image918×476 15.3 KB

Extremely grateful,

Iwona

1 Like
6

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

7

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

8

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.

9

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!