Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |