cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NoobAnalyst01 Regular Visitor
Regular Visitor

How to use crosstan in Power BI

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 TimeCapacity% 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:

 

Image 8.jpg

 

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

 

DateZoneStartTimeEventHourCustomersAll VisitUseful Visit
5/18/20180026:30:00 AM0244
5/18/20180026:30:00 AM5111
5/18/20180026:30:00 AM6599
5/18/20180026:30:00 AM771616
5/18/20180026:30:00 AM8122525
5/18/20180026:30:00 AM961515
5/18/20180026:30:00 AM10465
5/18/20180026:30:00 AM1171616
5/18/20180026:30:00 AM12355
5/18/20180028:00:00 AM0244
5/18/20180028:00:00 AM5111
5/18/20180028:00:00 AM6599
5/18/20180028:00:00 AM771616
5/18/20180028:00:00 AM8122523
5/18/20180028:00:00 AM961515
5/18/20180028:00:00 AM10465
5/18/20180028:00:00 AM117167
5/18/20180028:00:00 AM12350
5/18/20180136:00:00 AM0120
5/18/20180136:00:00 AM1340
5/18/20180136:00:00 AM2120
5/18/20180136:00:00 AM5490
5/18/20180136:00:00 AM661711
5/18/20180136:00:00 AM78169
5/18/20180136:00:00 AM815256
5/18/20180136:00:00 AM916280
5/18/20180136:00:00 AM1011172
5/18/20180136:00:00 AM1117280
5/18/20180136:00:00 AM124100
5/18/20180137:00:00 AM0122
5/18/20180137:00:00 AM1344
5/18/20180137:00:00 AM2122
5/18/20180137:00:00 AM5499
5/18/20180137:00:00 AM661717
5/18/20180137:00:00 AM781616
5/18/20180137:00:00 AM8152510
5/18/20180137:00:00 AM916282
5/18/20180137:00:00 AM1011170
5/18/20180137:00:00 AM1117280
5/18/20180137:00:00 AM124100

 

Thanks

Frank

4 REPLIES 4
Super User
Super User

Re: How to use crosstan in Power BI

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

NoobAnalyst01 Regular Visitor
Regular Visitor

Re: How to use crosstan in Power BI

Sample Data:

 

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

 

DateZoneStartTimeEventHourCustomersAll VisitUseful Visit
5/18/20180026:30:00 AM0244
5/18/20180026:30:00 AM5111
5/18/20180026:30:00 AM6599
5/18/20180026:30:00 AM771616
5/18/20180026:30:00 AM8122525
5/18/20180026:30:00 AM961515
5/18/20180026:30:00 AM10465
5/18/20180026:30:00 AM1171616
5/18/20180026:30:00 AM12355
5/18/20180028:00:00 AM0244
5/18/20180028:00:00 AM5111
5/18/20180028:00:00 AM6599
5/18/20180028:00:00 AM771616
5/18/20180028:00:00 AM8122523
5/18/20180028:00:00 AM961515
5/18/20180028:00:00 AM10465
5/18/20180028:00:00 AM117167
5/18/20180028:00:00 AM12350
5/18/20180136:00:00 AM0120
5/18/20180136:00:00 AM1340
5/18/20180136:00:00 AM2120
5/18/20180136:00:00 AM5490
5/18/20180136:00:00 AM661711
5/18/20180136:00:00 AM78169
5/18/20180136:00:00 AM815256
5/18/20180136:00:00 AM916280
5/18/20180136:00:00 AM1011172
5/18/20180136:00:00 AM1117280
5/18/20180136:00:00 AM124100
5/18/20180137:00:00 AM0122
5/18/20180137:00:00 AM1344
5/18/20180137:00:00 AM2122
5/18/20180137:00:00 AM5499
5/18/20180137:00:00 AM661717
5/18/20180137:00:00 AM781616
5/18/20180137:00:00 AM8152510
5/18/20180137:00:00 AM916282
5/18/20180137:00:00 AM1011170
5/18/20180137:00:00 AM1117280
5/18/20180137:00:00 AM124100
Super User
Super User

Re: How to use crosstan in Power BI

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

NoobAnalyst01 Regular Visitor
Regular Visitor

Re: How to use crosstan in Power BI

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)

 

 

Image 18.jpg

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

Image 19.jpg

 

 

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 967 guests
Please welcome our newest community members: