topic Re: Issue with Divide in Summarize table in DAX Commands and Tips

Issue with Divide in Summarize table

PS_78 — Thu, 27 Jun 2024 15:43:16 GMT

Hello - I am running into an issue with DIVIDE in Summarization. I have a table with list of tickets and I need to create a summarize table (creating summarize table as I have to do it for multiple measures) and calculate % of Bug fixes. For this, I am using DIVIDE function in SUMMARIZE table DAX function. This results in incorrect result. However, if I bring the numerator and denominator into the summarize table and create another measure using DIVIDE, it works fine. I am attaching herewith the sample PBIX file. Request your help to fix the formula in SUMMARIZE.

PBIX File - DIVIDE Issue with SUMMARIZE table

Thanks,

Phani

Re: Issue with Divide in Summarize table

lbendlin — Thu, 27 Jun 2024 23:30:21 GMT

Re: Issue with Divide in Summarize table

PS_78 — Fri, 28 Jun 2024 16:13:22 GMT

Thanks lbendlin for your response. I do not want to bring in "Incidents" and "# of tickets" into summarized table. I just want to do the actual calculation and use it in the visual.

Thanks,

Phani

Re: Issue with Divide in Summarize table

lbendlin — Fri, 28 Jun 2024 16:25:24 GMT

I thought that is what I provided. Please clarify what you are trying to achieve.

Re: Issue with Divide in Summarize table

PS_78 — Fri, 28 Jun 2024 16:39:49 GMT

Thanks for the quick response again. The solution you suggested is getting "Incidents" and "Total # of Tickets" in the summarized table and created measure within summarized table to calculate "% Bug Fixes_Msr". What I am trying to achieve is calculating "% Bug Fixes" within Summarized table itself.

In addition, the summarized table, "% Bug Fixes" is yielding incorrect ticket count. For example, Week W01 has only 7 bugs and 24 total tickets where as summarized table doubles it.

Thanks,

Phani

Re: Issue with Divide in Summarize table

lbendlin — Fri, 28 Jun 2024 17:05:57 GMT

% Bug Fixes = var a = ADDCOLUMNS(SUMMARIZE ( Tickets, [Created Year], [Created Week]), "Incidents", CALCULATE(COUNTROWS (Tickets),Tickets[Issue Type] = "Incident"), "# of Tickets", CALCULATE(COUNTROWS (Tickets),Tickets[Issue Type] in { "Incident", "Service request"}) ) return ADDCOLUMNS(a,"% Bug Fixes_Msr",DIVIDE([Incidents],[# of Tickets]))

Re: Issue with Divide in Summarize table

PS_78 — Fri, 28 Jun 2024 20:37:59 GMT

Thanks a lot lbendlin . This sounds great. I will accept this as a solution. But, is there any way, I can skip "Incidents" and "# of Tickets" from returning? I mean to ask, can we put count of Incidents / # of tickets in separate variables and use them in DIVIDE?

Thanks,

Phani

Re: Issue with Divide in Summarize table

lbendlin — Fri, 28 Jun 2024 23:04:34 GMT

Do you maybe need a measure instead of a calculated table?

Re: Issue with Divide in Summarize table

PS_78 — Mon, 01 Jul 2024 15:37:01 GMT

Hello lbendlin - The main table I have is raw data and I will have to generate multiple metrics (summarizing at week level) based on it, show each of the metric in matrix visual, combine it with a different dataset to get target into the calculated table, do the color code based on target Vs actual values. Hence I am going with calculated table.

Thanks,

Phani

Thanks,

Phani