cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Incorrect result when dividing with measure

Hey everyone,

I have devised a DAX function which basically checks if some conditions are satisified or not, if it satisifies the condition  then it divides the column with a dynamically changing measure( which is multiplication of no. of working days in a month with a constant).

Now if I divide the column with no. of working days in a month with a constant, manually, it gives me correct output, but with measure which changes dynamically it gives me wrong output.

My colleague checked every logica and function I used, nothing's wrong. Even for small value like 1.3 I'm getting 708.6. Everything works fine except the part where I am dividing with measure.

I have no idea what's wrong. Thought you guys might know what's wrong.

Thanks.

1 ACCEPTED SOLUTION

Hi @R_S-13697 ,

So the key point of this issue is to calculated the working days dynamically right?

Suppose you have a calendar date table and use it as slicer, now I select 2020 August, maybe you need measure like this:

Measure = 
var _workingdays = 
COUNTX(
    FILTER(
        ALLSELECTED('Calendar'),
        WEEKDAY('Calendar'[Date],2) <> 6 &&
        WEEKDAY('Calendar'[Date],2) <> 7
    ),
    'Calendar'[Date]
)
return
IF(
    SELECTEDVALUE('Table'[LSA]) = "LSA1" &&
    SELECTEDVALUE('Table'[Rate Type]) = "Onshore",
    CALCULATE(
        SUM('Table'[Hours Delivered]),
        FILTER(
            ALL('Table'),
            'Table'[LSA] = "LSA1" &&
            'Table'[Rate Type] = "Onshore"
        )
    ) / 
    (6.24 * _workingdays)
)

The variable in the formula will change when you select different date in the slicer and the total will change dynamically at the same time.

As for [hours delivered], I suppose it to calculate in the specific condition, for example, total of LSA1 when rate type = onshore.

 

Attached my sample file in the below, hopes to help you.

By the way, I would suggest you to create measures instead of calculated columns like your previous formula and using switch() function which would have better performance.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
Post Prodigy
Post Prodigy

Hi @R_S-13697 

 

When you are trying to calculate with a measure in a fact table row-by-row, you have to know the details about row context and how it interacts with measures (the so-called "context transition"). Because you most likely don't know it (as I can gather from your posts), you're getting wrong results.

 

On the other hand, using a measure to calculate columns in a big fact table is a big no-no. You should never do it. Because of context transition the calculation will be very slow (it can even throw out an out-of-mem error, which has been the topic of quite a few posts seen on this forum). What's more, calculated columns should be moved back to Power Query as this is the tool for rapid data munging. DAX is for data analysis. Worse, if you do it in DAX (especially on big fact tables) the compression will be extremely poor and hence the model will be bloated, DAX will be slower than it could be.

 

Hence my advice to you: please move the calculation to where it belongs - Power Query - and you'll be thankful that you did.

 

One last thing... There is a much better construct in DAX to express your logic: SWITCH. The IF is good when you have at most 2 conditions.

Super User IV
Super User IV

Please share your DAX expression, along with example input data and expected result.  You could use variables to troubleshoot to see what is being returned for the day count part.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi @mahoneypat ,
The issue-
Data available - 
Fact table has more than 70columns and 200000rows out of which the table I need to create in BI is concerened with only Service Period, Rate Type, LSA , Type of work, charge type and no. of hours delieverd.

Example-

LSAService PeriodRate TypeHours DelieverdType of workCharge type
LSA101-07-2014Onshore1Service for assistanceFixed
LSA201-12-2015Onshore0.5ProjectFixed
LSA301-06-2019Offshore2ProjectVariable
LSA201-09-2020Onshore12EnhancementVariable
LSA401-06-2019Offshore2MaintenanceFixed


Desired Output-

I need to create a table which calculates the no. of hours delivered based on Rate Type, Charge Type, Type of Work on the basis of Month and Year and LSA.
Example- Where the data changes dynamically with month and year

LSAFTE of Rate Type OnshoreFTE of Rate Type OffshoreFTE of Rate Type BlendedFTE for Application EnhancementFTE of work type Variable
LSA125.377.87.10.04.9
LSA226.41.623.82.661.1
LSA34.00.968.60.00.0
LSA41.346.220.90.023.7

 

Now these values are calculated using-
No. of hours of work delivered in a month(with conditions) divided by total no. of available working hours in a month. which is different for different LSAs. Example-
Total working hours in a month = no. of working hours in a dayfixed but different for Onshore and Offshore) * no. of working days in a month(changes dynamically)
LSA1(working hours in a month for LSA1) = 6.24 * 21(131.04) OR,
LSA2(working hours in a month for LSA2) = 6.52* 21(136.92)

 

What has be done so far-

Completed the table and logic except the final calculation part(wrong values I got).
DAX I have created -

FTE of Rate Type Onshore =
(
    IF (
        [Rate Type] == "Paid",
         (
            IF (
                [office] == "Farm",
                 [Hours delivered] / 157.08 ,
                 (
                    IF (
                        [office] == "Boult",
                        [Hours delivered] /127.68 ,
                         (
                            IF (
                                [office] == "Global",
                                [Hours delivered] / 136.92,
                                 (
                                    IF (
                                        [office] == "GOTS",
                                        [Hours delivered] / 131.04,
                                         (
                                            IF (
                                                [office] == "Daxnd",
                                                [Hours delivered] / 136.92,
                                                 (
                                                    IF (
                                                        [office] == "UK",
                                                        [Hours delivered] / 131.04,
                                                         (
                                                            IF (
                                                                [office] == "UTS",
                                                                [Hours delivered] / 131.04,
                                                                 (
                                                                    IF ( [office] == "ANZ", [Hours delivered] / 149.94, 0 )
)))))))))))))))))

131.04(working hours in a month) = 6.08(working hours in a day) * 21(August 2020->21 working days)
Working hours in a month will change dynamically with month.
Thing where I'm stuck-
I have devised logic for calculating no. of working days (a measure in fact table), using calendar table, but when I'm using it to divide it with sum of professional hours delivered. It does not produce an expected output. 
The code I'm using is given above.
Everything is working perfectly fine sperately, but when I'm using measure for division the output produced is wrong.

Help would be much appreciated I'm stuck here for last 2 days(just a beginner).
Thanks in advance for helping.

Hi @R_S-13697 ,

Based on your description, seems not certain how did it calulate, for example LSA1, 6.08 is the working hours per day, 21 is the working days in August 2014, but the service period of LSA1 I saw in your sample data is 2014/7/1. Did you want to calculate the total hours of the next month working days or anything else?

Could you please consider sharing more details about this calculation or a simple sample file based on the data picture and the sample expected output for further discussion? It could let us help you better to solve this issue.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey @v-yingjl 
The data set is too large that's why I used 5 columns as example.
The data set has data from January 2014 to September 2020 for each and every month of the year. 

Serive period has only the date of begining of each month irrespective of data(Ex- 1/1/14;1/2/14.........,1/9/20). Service period is just used to inform what month and year is being used. But we need to calculate total no. of working days in given month and multiply it with working hours in a day to get total working hours in a month.

Hi @R_S-13697 ,

So the key point of this issue is to calculated the working days dynamically right?

Suppose you have a calendar date table and use it as slicer, now I select 2020 August, maybe you need measure like this:

Measure = 
var _workingdays = 
COUNTX(
    FILTER(
        ALLSELECTED('Calendar'),
        WEEKDAY('Calendar'[Date],2) <> 6 &&
        WEEKDAY('Calendar'[Date],2) <> 7
    ),
    'Calendar'[Date]
)
return
IF(
    SELECTEDVALUE('Table'[LSA]) = "LSA1" &&
    SELECTEDVALUE('Table'[Rate Type]) = "Onshore",
    CALCULATE(
        SUM('Table'[Hours Delivered]),
        FILTER(
            ALL('Table'),
            'Table'[LSA] = "LSA1" &&
            'Table'[Rate Type] = "Onshore"
        )
    ) / 
    (6.24 * _workingdays)
)

The variable in the formula will change when you select different date in the slicer and the total will change dynamically at the same time.

As for [hours delivered], I suppose it to calculate in the specific condition, for example, total of LSA1 when rate type = onshore.

 

Attached my sample file in the below, hopes to help you.

By the way, I would suggest you to create measures instead of calculated columns like your previous formula and using switch() function which would have better performance.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

@v-yingjl 
I have solved this problem with the help of my colleague. Like you suggested we used Measure instead of columns and it's working fine except Totals .

I'm also checking your code if it fits I'll accept it as solution. Thanks a lot for your help.

P.S.- I will post the total problem in a new thread, if you want to have a look just let me know,I'll mention you there.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors