Best way to use % in table?

[Anonymous]

Hi all,

Currently have the issue of my measure tryign to sum my % instead of providing the % figure for all the data beaneth it. Has anyone got a clue how to fix this? I'd expect to see arouund 50% for the below instead of minus 109%. *Code in spoiler below photo*

Spoiler

Idle % = ((DISTINCTCOUNT('Agent Data'[Agent Name]) * 3600)-((sum('Agent Data'[On Calls Dur])+sum('Agent Data'[Unavailable Dur]))))/(DISTINCTCOUNT('Agent Data'[Agent Name]) * 3600)

Kind regards,

Jordan 

[Anonymous · Accepted solution]

In the end, we decided to remove the % all together from the row totals using HASONEFILTER. I don't believe this was the best method but it was A method. 

Idle = IF(HASONEFILTER('Interval'[Time]), ((DISTINCTCOUNT('Agent Data'[Agent Name]) * 3600)-((sum('Agent Data'[On Calls Dur])+sum('Agent Data'[Unavailable Dur]))))/(DISTINCTCOUNT('Agent Data'[Agent Name]) * 3600)) 

[v-diye-msft]

Hi @Anonymous 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[amitchandak]

@Anonymous , In case the issue is not resolved. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[amitchandak]

@Anonymous , Try like

Idle % = divide(((DISTINCTCOUNT('Agent Data'[Agent Name]) * 3600)-((sum('Agent Data'[On Calls Dur])+sum('Agent Data'[Unavailable Dur])))),
(DISTINCTCOUNT('Agent Data'[Agent Name]) * 3600))

Also refer wrong grand total
https://www.youtube.com/watch?v=ufHOOLdi_jk
https://www.youtube.com/watch?v=Rii_6qkLNh8
https://www.youtube.com/watch?v=Ka7Ds4EAjNQ

[jthomson]

Seems an obvious case of your formula being written specifically for a row by row basis which doesn't translate to a total context. Without seeing exactly how your data is formatted, I'd just write each of on calls/idle/unavailable to sum up the instances of each and then divide by the grand total, shouldn't be any need to multiply by 3600 or anything like that