How to use crosstan in Power BI

[NoobAnalyst01]

Hello,

I have build a dashboard which needs the crosstab.  I try to use Maxtrix, but it doen't help for my problem. The basically struction of my crosstab is like that:

		Number of Customer
		Event Hour
Start Time	Capacity	% Available

2 dimenstions as rows(start time and capacity), 2 dimenstion as columns(number of cutomers and event hour), %available as body.

My problem is I have to break this structure by each region(zone number) as below:

as you can see, although the struction is same, but each have different starttime, capacity, number of customer and event hours.

for example, for zone 002 it is have event hour=3(there is a record here, customer visite the webiste), for zone 013 there is no evet hour=3 in the column(because no customer visite this region at 3)

in my previous tool, I use BusinessObejcts, so I can create a break based on zone number or section based on zone number.

so can I do the same thing in PowerBi?

Sample Data:

Please note, in the column you should use formula Max(Customers) for customer column, and %avalible=useful visit/all visit

Date	Zone	StartTime	EventHour	Customers	All Visit	Useful Visit
5/18/2018	002	6:30:00 AM	0	2	4	4
5/18/2018	002	6:30:00 AM	5	1	1	1
5/18/2018	002	6:30:00 AM	6	5	9	9
5/18/2018	002	6:30:00 AM	7	7	16	16
5/18/2018	002	6:30:00 AM	8	12	25	25
5/18/2018	002	6:30:00 AM	9	6	15	15
5/18/2018	002	6:30:00 AM	10	4	6	5
5/18/2018	002	6:30:00 AM	11	7	16	16
5/18/2018	002	6:30:00 AM	12	3	5	5
5/18/2018	002	8:00:00 AM	0	2	4	4
5/18/2018	002	8:00:00 AM	5	1	1	1
5/18/2018	002	8:00:00 AM	6	5	9	9
5/18/2018	002	8:00:00 AM	7	7	16	16
5/18/2018	002	8:00:00 AM	8	12	25	23
5/18/2018	002	8:00:00 AM	9	6	15	15
5/18/2018	002	8:00:00 AM	10	4	6	5
5/18/2018	002	8:00:00 AM	11	7	16	7
5/18/2018	002	8:00:00 AM	12	3	5	0
5/18/2018	013	6:00:00 AM	0	1	2	0
5/18/2018	013	6:00:00 AM	1	3	4	0
5/18/2018	013	6:00:00 AM	2	1	2	0
5/18/2018	013	6:00:00 AM	5	4	9	0
5/18/2018	013	6:00:00 AM	6	6	17	11
5/18/2018	013	6:00:00 AM	7	8	16	9
5/18/2018	013	6:00:00 AM	8	15	25	6
5/18/2018	013	6:00:00 AM	9	16	28	0
5/18/2018	013	6:00:00 AM	10	11	17	2
5/18/2018	013	6:00:00 AM	11	17	28	0
5/18/2018	013	6:00:00 AM	12	4	10	0
5/18/2018	013	7:00:00 AM	0	1	2	2
5/18/2018	013	7:00:00 AM	1	3	4	4
5/18/2018	013	7:00:00 AM	2	1	2	2
5/18/2018	013	7:00:00 AM	5	4	9	9
5/18/2018	013	7:00:00 AM	6	6	17	17
5/18/2018	013	7:00:00 AM	7	8	16	16
5/18/2018	013	7:00:00 AM	8	15	25	10
5/18/2018	013	7:00:00 AM	9	16	28	2
5/18/2018	013	7:00:00 AM	10	11	17	0
5/18/2018	013	7:00:00 AM	11	17	28	0
5/18/2018	013	7:00:00 AM	12	4	10	0

Thanks

Frank

[Greg_Deckler]

Use a Matrix and put Zone and StartTime in the Rows in that order. Put EventHour in Columns. Put Customers in Values. Now, go into the format painter and under Row Headers, turn off Stepped layout. Use the branching drilldown to drill down to StartTime.

[NoobAnalyst01]

All most here, but

My Measure = CALCULATE(MAX('Report 1'[NUMBER_OF_CUSTOMERS]),ALLEXCEPT('Report 1','Report 1'[ZONE],'Report 1'[EVENT_HOUR])) (Get Max number of customer in each zone, and event hour)

I shold put this measure at the column, beucase I have %avlibiy should put in as Value, so it will looks like

each zone should have seperate section, becasue they canont share the same column, like you can see, the Max nunmber of customer is different, but I have 30+ different zone, so I cannot create 30 differnt Maxtrix and filter them one by one.

[Greg_Deckler]

Can you post sample/example source data that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[NoobAnalyst01]

Sample Data:

in the column for customer by event hour, should use the formual Max(Customers), and the % avalible= sum(UsefulVist)/sum(All Visit)

Date	Zone	StartTime	EventHour	Customers	All Visit	Useful Visit
5/18/2018	002	6:30:00 AM	0	2	4	4
5/18/2018	002	6:30:00 AM	5	1	1	1
5/18/2018	002	6:30:00 AM	6	5	9	9
5/18/2018	002	6:30:00 AM	7	7	16	16
5/18/2018	002	6:30:00 AM	8	12	25	25
5/18/2018	002	6:30:00 AM	9	6	15	15
5/18/2018	002	6:30:00 AM	10	4	6	5
5/18/2018	002	6:30:00 AM	11	7	16	16
5/18/2018	002	6:30:00 AM	12	3	5	5
5/18/2018	002	8:00:00 AM	0	2	4	4
5/18/2018	002	8:00:00 AM	5	1	1	1
5/18/2018	002	8:00:00 AM	6	5	9	9
5/18/2018	002	8:00:00 AM	7	7	16	16
5/18/2018	002	8:00:00 AM	8	12	25	23
5/18/2018	002	8:00:00 AM	9	6	15	15
5/18/2018	002	8:00:00 AM	10	4	6	5
5/18/2018	002	8:00:00 AM	11	7	16	7
5/18/2018	002	8:00:00 AM	12	3	5	0
5/18/2018	013	6:00:00 AM	0	1	2	0
5/18/2018	013	6:00:00 AM	1	3	4	0
5/18/2018	013	6:00:00 AM	2	1	2	0
5/18/2018	013	6:00:00 AM	5	4	9	0
5/18/2018	013	6:00:00 AM	6	6	17	11
5/18/2018	013	6:00:00 AM	7	8	16	9
5/18/2018	013	6:00:00 AM	8	15	25	6
5/18/2018	013	6:00:00 AM	9	16	28	0
5/18/2018	013	6:00:00 AM	10	11	17	2
5/18/2018	013	6:00:00 AM	11	17	28	0
5/18/2018	013	6:00:00 AM	12	4	10	0
5/18/2018	013	7:00:00 AM	0	1	2	2
5/18/2018	013	7:00:00 AM	1	3	4	4
5/18/2018	013	7:00:00 AM	2	1	2	2
5/18/2018	013	7:00:00 AM	5	4	9	9
5/18/2018	013	7:00:00 AM	6	6	17	17
5/18/2018	013	7:00:00 AM	7	8	16	16
5/18/2018	013	7:00:00 AM	8	15	25	10
5/18/2018	013	7:00:00 AM	9	16	28	2
5/18/2018	013	7:00:00 AM	10	11	17	0
5/18/2018	013	7:00:00 AM	11	17	28	0
5/18/2018	013	7:00:00 AM	12	4	10	0