Hi there,
Im a new PBI user and have been having a lot of trouble with this software.
I want to try to filter a visual (Matrix view of hourly data) based on percentages of the customer's overall percentage (based off of a selected time frame). But when i filter it on that visual, it converts the percentage into hourly values.
This has to do with context I believe.
Refer to the tables below, I can filter visual B correctly when a user enters a percent. But I cannot filter Visual A properly.
To futher clarify, if I enter 80% only 1 customer would appear. If I enter 20%, then 3 customers would appear.
I would like Visual A to show the corresponding customers like Visual B.
What I've tried something like this....this code below, sort of works, but brings new issues.
test=summarize(table,"rowcount", calculate(countrows(allselected(table)),allexcept(table, Date[fiscalyear], table[customers])))
Filter =
Var value = percent[percentvalue]/100
var pickedvalue= test/table[totalhourlycount]
return
if (pickedvalue>=value,1,0)
This above sort of works but i have pay attention to 2 things.
1) This works only for fiscal years, if I were to enter a custom date range that is anything but a fiscal year, the above does NOT work. (This is because I just so happen to select the date range of a fiscal year, but custom date ranges will NOT work)
2) I have to change the rows and columns. I originally have Date on Rows, and Customers on columns, but if I swapped them. Then the filtering works. BUT this leads to other problems.
a) The formatting looks weird
b) the columns are only limited to 100 columns. For hourly data, theres 8760 hours in 1 year.
I did find a work around but using inforiver. For some reason I do not know why, I can enter the data into inforiver and can be filtered properly in the correct format.
The issue of the custom date still remains.
And a new issue that I cannot export this without paying a subscription
I would like to know how to do this properly without inforiver, with custom dates, with the proper formatting.
Thanks in advanced!
Visual A
| Date | Customer 1 | Customer 2 | Customer 3 | Customer 4 | Customer 5 |
| 1/1/2000 12:00 | 1 | | | | |
| 1/1/2000 13:00 | 1 | | | | |
| 1/1/2000 14:00 | 1 | | | | |
| 1/1/2000 15:00 | 2 | 2 | | | |
| 1/1/2000 16:00 | 3 | 4 | | | |
| 1/1/2000 17:00 | 4 | 4 | 2 | | |
| 1/1/2000 18:00 | 5 | 3 | 1 | | |
| 1/1/2000 19:00 | 3 | 2 | 1 | 1 | 1 |
Visual B
| | Customer 1 | Customer 2 | Customer 3 | Customer 4 | Customer 5 |
Customer data
count | 8 | 5 | 3 | 1 | 1 |
Percent data (customer data count/total hourly count) | 100 | 62.5 | 37.5 | 12.5 | 12.5 |
Total hourly count (The date range shown in the example has 8 hours, so i would do a datediff (min, max)) |
| 8 |
User enters the % (in this case, 80 was entered)
Desired results based on the 80%
Visual A
| Date | Customer 1 |
| 1/1/2000 12:00 | 1 |
| 1/1/2000 13:00 | 1 |
| 1/1/2000 14:00 | 1 |
| 1/1/2000 15:00 | 2 |
| 1/1/2000 16:00 | 3 |
| 1/1/2000 17:00 | 4 |
| 1/1/2000 18:00 | 5 |
| 1/1/2000 19:00 | 3 |
Visual B
| | Customer 1 |
Customer data
count | 8 |
| Percent data | 100 |
