Implementing QlikSense logic into a Power BI DAX formula

[ManchevB]

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

[ManchevB · Accepted solution]

I was able to adjust the formula myself

[Anonymous]

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