Forum Discussion

TARUGOKING's avatar
TARUGOKING
Helper I
1 year ago
Solved

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 [Trip...
Need Help
Show and Tell
  • TARUGOKING's avatar
    TARUGOKING
    1 year ago

    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!

     

     

Ashish_Excel's avatar
Ashish_Excel
Solution Supplier
1 year ago

Hi,

I have solved a similar question in this file.

Hope this helps.

  • TARUGOKING's avatar
    TARUGOKING
    Helper I
    1 year ago

    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

    • TARUGOKING's avatar
      TARUGOKING
      Helper I
      1 year ago

      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!