Issue with Divide in Summarize table

[PS_78]

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

[lbendlin]

[PS_78]

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

[lbendlin]

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

[PS_78]

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

[lbendlin · Accepted solution]

% 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]))

[PS_78]

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

[lbendlin]

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

[PS_78]

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