Highly customized columns crosstab from cognos to power bi Matrix

[Alrythmond]

Hi there I have a request from my seniors that I need to replicate a crosstab to powerbi see screenshot below.

as you can see there is a goal column and some trend icons also, at the very start of the table and two more separate columns at the end which are not related to the columns in the middle, is there anyway to achieve this? 

[v-xicai]

Hi @Alrythmond ,

You can follow steps below to meet your demand, change the name of tables or fields to take it effective.

Firstly, calculate FTE Month Ave and FTE Year Ave.

Assuming An month FTE is considered to be 160 hours, which is calculated as: 8 hours per day * 5 work days per week *4 weeks per month= 160 hours per month, and an annual FTE is considered to be 1920 hours, which is calculated as:8 hours per day *5 work days per week *48 weeks per year= 1920 hours per year. Then create columns using DAX below.

Column: FTE Month Ave=DIVIDE(Fact_Table[Net Capacity Hour MTD]-Fact_Table[Absence Hour MTD],8*5*4)

Column: FTE Year Ave=DIVIDE(Fact_Table[Net Capacity Hour YTD]-Fact_Table[Absence Hour YTD],8*5*52)

Secondly, calculate Headcount Month Ave and Headcount Year Ave.

Create new table DimDate which is a consistent date calendar table, and create columns MonthNumber and YearNumber .

Table: DimDate =CALENDARAUTO()

Columns:

MonthNumber = Month(Fact_Table[Datetime])

YearNumber = Year(Fact_Table[Datetime])

Measures:

Headcount Month Ave=

var Headcount = SUMX (SUMMARIZE (Fact_Table,Fact_Table[Dim1], Fact_Table[Dim2], Fact_Table[Dim3], ..."Headcount", DISTINCTCOUNT(Fact_Table[EmployeeCode])), [Headcount])

var MonthCount = CALCULATE(DISTINCTCOUNT(Fact_Table[MonthNumber]),FILTER(DimDate, MONTH(DimDate[FullDateAlternateKey]) <= MONTH(TODAY())))

Return DIVIDE(Headcount, MonthCount)

Headcount Year Ave=

var Headcount = SUMX (SUMMARIZE (Fact_Table,Fact_Table[Dim1], Fact_Table[Dim2], Fact_Table[Dim3], ..."Headcount", DISTINCTCOUNT(Fact_Table[EmployeeCode])), [Headcount])

var YearCount = CALCULATE(DISTINCTCOUNT(Fact_Table[YearNumber ]),FILTER(DimDate, YEAR(DimDate[FullDateAlternateKey]) <= YEAR(TODAY())))

Return DIVIDE(Headcount, YearCount )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alrythmond]

Hi I am using matrix, won't the columns be affected if I put data on the columns section of the matrix? for example if I apply it to one column on the matrix it will apply to all the columns of the matrix

[v-xicai]

Hi@Alrythmond ,

For the FTE Month Ave and FTE Year Ave , you can also use measure, while you can add MAX()function before it to take measure work.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alrythmond]

Sorry but I am kind of confused, will it be okay if you show me a sample of the output you are telling me?

[v-xicai]

Hi @Alrythmond ,

I am sorry to make you confused.You can create measures like this. 

FTE Month Ave=DIVIDE(MAX(Fact_Table[Net Capacity Hour MTD])-MAX(Fact_Table[Absence Hour MTD]),8*5*4)

FTE Year Ave=DIVIDE(MAX(Fact_Table[Net Capacity Hour YTD])-MAX(Fact_Table[Absence Hour YTD]),8*5*52)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alrythmond]

okay I know now how to create the columns, but how do I combine all of this data into one matrix like the picture above.

NOTE: that the picture displayed is a cognos BI crosstab, not a power bi matrix

[v-xicai]

Hi,

You can drag these field columns or measures to values or Columns of Matrix visual in the Fields pan .

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alrythmond]

hi @v-xicai 

but if I do that it will be applied to all the columns.

[v-xicai]

Hi Alrythmond,

Would you like to create new two columns named FTE and Hourcount using DAX? If yes ,could you please show me the logical between the new columns with the columns in the middle or goal column for further analysis?

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alrythmond]

Hi There thank you for your reply,

yes if it is possible with dax I would want the two columns at the end to be that way since I can't find any other way, the logic for the two columns at the end will contain the ave hours of the employees per month and year for the FTE column, and the number of employees for the headcount column, so they are totally different values from the columns in the middle,

for the goal column at the very start, it is sorta like a quota that they need to achieve which will be the base of the comparison for the trend icons beside WTD MTD and YTD columns underthe Utilizations column

also if possible all of the data in the screenshot will be displayed in a matrix in power BI