cancel
Showing results for
Did you mean:
Highlighted
PowerQA1 Frequent Visitor

## Multiple Calculated fields displaying monthly stacked column chart

Hi Guys,

I have two calculations which are working fine.

One calculates the number of defects written monthly - using the stacked column chart

• Axis - Detected On Date (Date defect was found)
• Value - Count of Detected on Date (Count of Defects)

Second calculates the number of test cases executed monthly - using the stacked column chart

• Axis - Executed Date (Date cases were executed)
• Value - Test Case Count (Count of executed test cases)

Now I need to calculate the TCE monthly which is the Monthly Defects / Monthly Cases Executed and plot this monthly in a bar chart

• Using the stacked column chart can't get the monthly numbers to calculate correctly

TCE Calc

• Axis - Detected On Date (Date defect was found)
• Value - ILI Defect Count divided by ILI Case Count (DAX Below)

DAX

ILI Defect Count divided by ILI Case Count = DIVIDE([ILI Defect Count], [ILI Case Count])

Results Showing in Graph

• Jan - .70%
• Feb - .57%
• Mar - .54%
• Apr - 1.13%
• May - .22%

If I switch the Axis from Detected On Date (Date defect was found) to Executed Date (Date cases were executed)

Results Showing in Graph

• Jan - 14.22%
• Feb - 20.65%
• Mar - 11.83%
• Apr - 16.37%
• May - 19.39%

Correct Values

• Jan - 3.1% (45/1435)
• Feb - 3.7% (37/988)
• Mar - 2.0% (35/1724)
• Apr - 5.8% (73/1246)
• May - 1.3% (14/1052)

Not sure what to do any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
RApbix Visitor

## Re: Multiple Calculated fields displaying monthly stacked column chart

Here is a query you can use to create a calendar.  Link the Date field from your table to the Month field from the calendar. This should help with your calculation.

= (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>

let

DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),

InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),

InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),

InsertFinYearNumber = Table.AddColumn(InsertMonth, "FinYearNumber",each if [MonthOfYear] >= 7  then [Year] else [Year] -1 ),

InsertFinYearDisplay = Table.AddColumn(InsertFinYearNumber, "FinYear" ,each "FY" & Text.End(Number.ToText([FinYearNumber],"D",""),2) & "/" & Text.End(Number.ToText([FinYearNumber]+1,"D",""),2)),

InsertFinMonth = Table.AddColumn(InsertFinYearDisplay, "FinMonth", each if [MonthOfYear] >= 7  then [MonthOfYear] - 6 else [MonthOfYear] + 6 ),

InsertFinQuarterNumber = Table.AddColumn(InsertFinMonth, "FinQuarterNumber", each if [QuarterOfYear] > 2 then [QuarterOfYear] -2 else [QuarterOfYear] + 2),

InsertFinQuarterDisplay = Table.AddColumn(InsertFinQuarterNumber ,"FinQuarter", each "FQ" & Number.ToText([FinQuarterNumber],"D","") ),

InsertDay = Table.AddColumn(InsertFinQuarterDisplay , "DayOfMonth", each Date.Day([Date])),

InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),

InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),

InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),

InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),

InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),

InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),

InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),

InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,

if [CurrentThursday] < [ISOWeekJan4]

,type date),

InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),

InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),

InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10)  + [DayInWeek]),3)),

InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),

fnPeriod445a = (weekNum) => let

Periods =

{

{(x)=>x<5,  [P=1,Q=1]},

{(x)=>x<9,  [P=2,Q=1]},

{(x)=>x<14, [P=3,Q=1]},

{(x)=>x<18, [P=4,Q=2]},

{(x)=>x<22, [P=5,Q=2]},

{(x)=>x<27, [P=6,Q=2]},

{(x)=>x<31, [P=7,Q=3]},

{(x)=>x<35, [P=8,Q=3]},

{(x)=>x<40, [P=9,Q=3]},

{(x)=>x<44, [P=10,Q=4]},

{(x)=>x<48, [P=11,Q=4]},

{(x)=>true, [P=12,Q=4]}

},

Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}

in

Result,

InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),

ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),

RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})

in

RemovedColumns

8 REPLIES 8 Super User

## Re: Multiple Calculated fields displaying monthly stacked column chart

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
PowerQA1 Frequent Visitor

## Re: Multiple Calculated fields displaying monthly stacked column chart

My company does not allow me to share files.

Here is some sample data

There are two tables

• One table has typical Defect data
• One table has typical Execution data

Defect Data Table Sample

 Defect ID Application Provder Project Release Priority Status System Summary Detected On 726 App 1 Comp 1 Changes Output February 3-High Closed Output Bad Output 1/31/2019 888 App 2 Comp 2 Input Changes May - 19 5-Urgent Closed Input Bad Input 3/12/19

Exectution Data Table Sample

 Application Test ID Exec Date Test: Execution Status Name Project Release Status Subject App1 856531 1/23/2019 Passed Test 1 Project 1 Jan Passed Subject 1 App2 855625 1/10/2019 Passed Test 2 Project 2 18L-December+ Passed Subject 2 App3 854618 1/18/2019 Passed Test 3 Project 3 19B-February Passed Subject 3 Super User

## Re: Multiple Calculated fields displaying monthly stacked column chart

Hi,

Based on the 2 Tables shared by you, please show the expected result in a new Table (3 columns - Date and expected figure from figure 1 and expected figure from figure 2).  Once the Table is ready, one can simply switch to one's desired visual.

Regards,
Ashish Mathur
http://www.ashishmathur.com
PowerQA1 Frequent Visitor

## Re: Multiple Calculated fields displaying monthly stacked column chart Detected On Date Field Used in TCE Chart

The TCE chart

• Is just a simple division of the defects / test cases
• It's not working because they are two differnt tables of data & the chart is using the date field to display them monthly
• DAX used in the Value Field - TCE = IF(ISBLANK([Defect Count]/'TCE'[Case Count),0,[Defect Count]/'TCE'[Case Count])

These are the correct values which I want in the TCE Chart

Jan - 3.1% (45/1435)

Feb - 3.7% (37/988)

Mar - 2.0% (35/1724)

Apr - 5.8% (73/1266)

May - 1.3% (14/1052)

PowerQA1 Frequent Visitor

## Re: Multiple Calculated fields displaying monthly stacked column chart TCE Chart Values if Exec Date Field used as Axis Super User

## Re: Multiple Calculated fields displaying monthly stacked column chart

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
PowerQA1 Frequent Visitor

## Re: Multiple Calculated fields displaying monthly stacked column chart

Hi Ashsish

Is there any other way you could share the information

RApbix Visitor

## Re: Multiple Calculated fields displaying monthly stacked column chart

Here is a query you can use to create a calendar.  Link the Date field from your table to the Month field from the calendar. This should help with your calculation.

= (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>

let

DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),

InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),

InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),

InsertFinYearNumber = Table.AddColumn(InsertMonth, "FinYearNumber",each if [MonthOfYear] >= 7  then [Year] else [Year] -1 ),

InsertFinYearDisplay = Table.AddColumn(InsertFinYearNumber, "FinYear" ,each "FY" & Text.End(Number.ToText([FinYearNumber],"D",""),2) & "/" & Text.End(Number.ToText([FinYearNumber]+1,"D",""),2)),

InsertFinMonth = Table.AddColumn(InsertFinYearDisplay, "FinMonth", each if [MonthOfYear] >= 7  then [MonthOfYear] - 6 else [MonthOfYear] + 6 ),

InsertFinQuarterNumber = Table.AddColumn(InsertFinMonth, "FinQuarterNumber", each if [QuarterOfYear] > 2 then [QuarterOfYear] -2 else [QuarterOfYear] + 2),

InsertFinQuarterDisplay = Table.AddColumn(InsertFinQuarterNumber ,"FinQuarter", each "FQ" & Number.ToText([FinQuarterNumber],"D","") ),

InsertDay = Table.AddColumn(InsertFinQuarterDisplay , "DayOfMonth", each Date.Day([Date])),

InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),

InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),

InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),

InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),

InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),

InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),

InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),

InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,

if [CurrentThursday] < [ISOWeekJan4]

,type date),

InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),

InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),

InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10)  + [DayInWeek]),3)),

InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),

fnPeriod445a = (weekNum) => let

Periods =

{

{(x)=>x<5,  [P=1,Q=1]},

{(x)=>x<9,  [P=2,Q=1]},

{(x)=>x<14, [P=3,Q=1]},

{(x)=>x<18, [P=4,Q=2]},

{(x)=>x<22, [P=5,Q=2]},

{(x)=>x<27, [P=6,Q=2]},

{(x)=>x<31, [P=7,Q=3]},

{(x)=>x<35, [P=8,Q=3]},

{(x)=>x<40, [P=9,Q=3]},

{(x)=>x<44, [P=10,Q=4]},

{(x)=>x<48, [P=11,Q=4]},

{(x)=>true, [P=12,Q=4]}

},

Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}

in

Result,

InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),

ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),

RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})

in

RemovedColumns

Announcements #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content. #### Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones. #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 451 members 4,548 guests
Recent signins:
• SanjeevMurthy • mvelazquez • pankajj • Bare • verwegh • achandlerworth • Daviejoe • DaGoob • chrisasmith • alextouli • polletr • geraintjames • rjweed01 • Srinivaspappu19 