Forum Discussion
How do I do this in PBI?
HI,
I'm trying to create this report in PBI. In SQL, it would look like this:
I loaded my dataset as TEST2 but I can only generate the TripCount per month summary. I created a table [TripCountPerMonth] and I have the correct TripCount By Month By Name. How do I evaluate and pivot it to get the desire T-SQL result or is there a better way just using the [TEST2 ] table?
Final DS is multi year. Just
--- DATASET ---
Name,OMTripDate,TripCount
APPLE,08/17/2024,1
APPLE,09/25/2024,1
APPLE,10/19/2024,1
APPLE,11/02/2024,1
APPLE,12/04/2024,1
MANGO,12/07/2024,1
MANGO,12/20/2024,1
MANGO,01/18/2024,1
MANGO,01/22/2024,1
MANGO,02/05/2024,1
MANGO,02/09/2024,1
MANGO,02/26/2024,1
APPLE,03/04/2024,1
BANANA,03/06/2024,1
ORANGE,02/19/2024,1
MANGO,03/12/2024,1
MANGO,02/28/2024,1
MANGO,02/29/2024,1
MANGO,03/04/2024,1
ORANGE,03/18/2024,1
MANGO,03/14/2024,1
MANGO,03/21/2024,1
MANGO,03/26/2024,1
MANGO,03/28/2024,1
MANGO,04/05/2024,1
ORANGE,04/03/2024,1
MANGO,04/24/2024,1
MANGO,04/09/2024,1
MANGO,04/10/2024,1
APPLE,04/25/2024,1
BANANA,04/17/2024,1
ORANGE,04/18/2024,1
ORANGE,04/23/2024,1
MANGO,04/30/2024,1
BANANA,05/06/2024,1
ORANGE,04/30/2024,1
BANANA,05/02/2024,1
ORANGE,05/03/2024,1
ORANGE,05/06/2024,1
MANGO,05/07/2024,1
BANANA,06/10/2024,1
MANGO,05/09/2024,1
MANGO,05/14/2024,1
ORANGE,05/09/2024,1
MANGO,05/23/2024,1
APPLE,06/10/2024,1
MANGO,06/08/2024,1
ORANGE,06/12/2024,1
MANGO,06/11/2024,1
BANANA,06/18/2024,1
MANGO,06/18/2024,1
MANGO,06/16/2024,1
BANANA,06/24/2024,1
APPLE,06/26/2024,1
MANGO,06/25/2024,1
MANGO,06/26/2024,1
MANGO,07/02/2024,1
MANGO,07/03/2024,1
MANGO,07/05/2024,1
MANGO,07/08/2024,1
MANGO,07/17/2024,1
MANGO,07/22/2024,1
MANGO,07/23/2024,1
MANGO,07/25/2024,1
BANANA,08/23/2024,1
APPLE,10/09/2024,1
TIA
So I created the MIN MAX table:
On my Summary Table, I added a range column that is linked to the MIN MAX table:
On my Martrix:
or PIVOT
Thanks 2 all!
5 Replies
See attached PBIX.
Most of the work done in PowerQuery but you could move this upstream to SQL.
Not quite but almost there. The Matrix wasn't matching on the range count but the totals are correct.
VS
Your approach did give me an idea. I moved the dataset to SQL and pulled it in (since I needed to deliver the report due to time constraints). Thank you.
I like this approach but I can't seem to correctly map it. Sampling June, I have 4 counts and should hit the following range as below:
However, I'm getting it across the board.
So I'm trying to figure out how to fix the script. Any ideas what I'm doing wrong (new to PBI)?
TripByRange = COUNTROWS(FILTER(ADDCOLUMNS(VALUES(TEST2_TripCountPerMonth[TripCountByMonth]),"ABCD",COUNTROWS(CALCULATETABLE(VALUES(TEST2_TripCountPerMonth[Name])))),COUNTROWS(FILTER('TEST2_RANGE',[ABCD] >= 'TEST2_RANGE'[Min] && [ABCD] <= 'TEST2_RANGE'[Max]))>=0 ))TIA
So I created the MIN MAX table:
On my Summary Table, I added a range column that is linked to the MIN MAX table:
On my Martrix:
or PIVOT
Thanks 2 all!
