Forum Discussion

ManchevB's avatar
ManchevB
Icon for Helper II rankHelper II
2 years ago
Solved

Implementing QlikSense logic into a Power BI DAX formula

Hey, 

 

I created a utilization formula in Power BI, that gives me information on the utilization of a single engineer. What I am looking to do next is to have a view on a monthly level - for each country. 

 

The problem is that when I visualize my formula for the "UI %" together with Month in a chart, I get a crazy number: 

 

 

The formula for the UI % is: 

UI % calling country value =
DIVIDE(
    [Total Time in Hours equal or less than 14],
    [Distinct Count of Onsite_Date_WO]
) / MAXX('Hours3', 'Hours3'[Standard Hours Per Country])  
 
* the logic is that I am dividing the Total Time in Hours by the Onsite Dates by the Standard Working Hours per Country 
 
The logic works fine and shows the correct result when I want to visualize it for an engineer only, but when I want to compare it against his peers or on the country he is from, I get a number that doesn't match. 
 
I believe this might have something to do with aggregation or weighted average but I am still learning and cannot be sure. 
 
Any help would be greatly appreciated here!
 
Happy to share more details if needed,
Boris

 

Need Help

2 Replies

  • ManchevB's avatar
    ManchevB
    Icon for Helper II rankHelper II
    2 years ago

    I was able to adjust the formula myself

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ManchevB 

     

    This is usually due to that the filter context has changed when filtering. As the measure consists of three parts: [Total Time in Hours equal or less than 14], [Distinct Count of Onsite_Date_WO] and MAXX('Hours3', 'Hours3'[Standard Hours Per Country]), you can add these parts to card visuals individually to check which part doesn't return the correct result. Then you can try to correct each part one by one to get the final result. Separating and testing each part can make it easier to verify the DAX expression.

     

    If possible, can you provide some dummy data in table format and share the desired correct outcome? We can then use the dummy data to create a demo and test any possible DAX formula. 

     

    Best Regards,
    Jing