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

Multiple Calculated fields displaying monthly stacked column chart

Hi Guys,

 

Newbie here to DAX..  Appreciate any help you can give.

 

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
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),   

    InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), 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])) ,  

    InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each

        if [CurrentThursday] < [ISOWeekJan4]

        then Date.AddDays([CurrentThursday],-3)

        else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )

      ,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

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Multiple Calculated fields displaying monthly stacked column chart

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PowerQA1 Frequent Visitor
Frequent Visitor

Re: Multiple Calculated fields displaying monthly stacked column chart

Thanks for your help...

 

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 IDApplicationProvderProjectReleasePriorityStatusSystemSummaryDetected On 
726App 1Comp 1Changes OutputFebruary3-HighClosedOutput Bad Output1/31/2019
888App 2Comp 2Input ChangesMay - 195-UrgentClosedInputBad Input3/12/19

 

 

Exectution Data Table Sample

 

Application Test IDExec DateTest: Execution StatusNameProject ReleaseStatusSubject
App18565311/23/2019PassedTest 1Project 1JanPassedSubject 1
App28556251/10/2019PassedTest 2Project 218L-December+PassedSubject 2
App38546181/18/2019PassedTest 3Project 319B-FebruaryPassedSubject 3

 

 

Super User
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
https://www.linkedin.com/in/excelenthusiasts/
PowerQA1 Frequent Visitor
Frequent Visitor

Re: Multiple Calculated fields displaying monthly stacked column chart

 

 

TCE Values Exec Date.pngDetected 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
Frequent Visitor

Re: Multiple Calculated fields displaying monthly stacked column chart

TCE Values Exec Date.pngTCE Chart Values if Exec Date Field used as Axis

Super User
Super User

Re: Multiple Calculated fields displaying monthly stacked column chart

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PowerQA1 Frequent Visitor
Frequent Visitor

Re: Multiple Calculated fields displaying monthly stacked column chart

Hi Ashsish

 

Thanks so much for your help, but my company does not give me access to that area to download a file.

 

Is there any other way you could share the information

RApbix Visitor
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),   

    InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), 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])) ,  

    InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each

        if [CurrentThursday] < [ISOWeekJan4]

        then Date.AddDays([CurrentThursday],-3)

        else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )

      ,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

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

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.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

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

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 451 members 4,548 guests
Please welcome our newest community members: