Forum Discussion

cewjr9842's avatar
cewjr9842
Frequent Visitor
6 years ago
Solved

Issues merging values and columns in PBI

Good Day People!

 

First let me state my premise. I am currently building a report as well as this is my first time using PBI to manipulate data and its a pretty cool tool. When i say first time using PBI, i mean to build a report. Creating a Dashboard seems pretty simple to me and I have done that before. But, with this report I am trying create a decisioning monitor report to later create a flow that sends emails to our team as the results update. 

 

Now I am running this from a query and the results are shown below:

 

Time bucket:

-1=Missing Applications

30= 30 Seconds

45= 45 Seconds

60= 60 secs

120= 2 minutes

300=5 minutes

2400= 20 minutes

9999= 20 minutes or more

 

App_days:

are the days of the month

 

app_hours:

Are the hours of the day in 15 minute incriments also in miltary time.

 

n:

Are the aplications that have come in and have been confirmed decisioned.

 

 

The the purpose of this is to build a refreshable report that will tell me in percentages, have been decisioned and by day.

I have a picture of the matrix report that I currently have built in Matrix Form below:

 

What I want to do is combine the Columns to show them as this below:

 

 

So I want to merge the columns as such:

  • columns30 and label it <30 with the calculated results below the column 
  • columns30, 45 and 60 and Label it 30-60 with the calculated results below the column for the columns
  • columns60 and 120 and label it 1-2Minutes with the calculated results below the column for the columns
  • Columns300 I will keep the same and label it >2 min with calulated results below the column for the columns
  • column2400 labeled as >20min with calulated results below the column

 

I was wondering if this was at all possible with the query I have or is there a way to manipulate the data or rewrite a query with in power bi that could help me achieve this. I cna provide more information if needed for a better understanding if this was not written correctly. 

 

I have this report in Excel, but I want to create a report similiar to the one below i created.

 

 

I have tried new measures but I really suck at writing them to be honest. 

I am not the greatest at PBI at all and some help would surely be appreciated. Also this is live data coming from SQL, so I know query maniuplation may have to be done, But I would rather do it PBI than mess with the original query in SQL. I am not sure if value manipulation is even achievable via measure changes.  I can also provide the query or excel data to see what I actually am looking at.

 

 

Thanks in advance

 

 

 

General Comment
Show and Tell
Tips and Tricks
Tutorial Requests
  • Icey's avatar
    Icey
    6 years ago

    Hi cewjr9842 ,

    For the chart, you can create it like so:

     

    For the Matrix, you can create it like so:

    1. Enter a table "Range" and sort "Range" column by column "Index".

    2. Create a measure.

    Sum of n = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Range[Range] ) = "Missing", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] = -1 
    ),
    SELECTEDVALUE ( Range[Range] ) = "<30", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] <= 30 
    ),
    SELECTEDVALUE ( Range[Range] ) = "30-60", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 30
            && 'Table'[time_bucket] <= 60
    ),
    SELECTEDVALUE ( Range[Range] ) = "1-2 min", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 60
            && 'Table'[time_bucket] <= 120
    ),
    SELECTEDVALUE ( Range[Range] ) = ">2 min", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 120
            && 'Table'[time_bucket] <= 2400
    ),
    SELECTEDVALUE ( Range[Range] ) = ">20 min", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] > 2400 
    ),
    SELECTEDVALUE ( Range[Range] ) = BLANK (), 
    SUM ( 'Table'[n] )
)

    3. Create the Matrix.

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi cewjr9842 ,

    For the chart, you can create it like so:

     

    For the Matrix, you can create it like so:

    1. Enter a table "Range" and sort "Range" column by column "Index".

    2. Create a measure.

    Sum of n = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Range[Range] ) = "Missing", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] = -1 
    ),
    SELECTEDVALUE ( Range[Range] ) = "<30", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] <= 30 
    ),
    SELECTEDVALUE ( Range[Range] ) = "30-60", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 30
            && 'Table'[time_bucket] <= 60
    ),
    SELECTEDVALUE ( Range[Range] ) = "1-2 min", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 60
            && 'Table'[time_bucket] <= 120
    ),
    SELECTEDVALUE ( Range[Range] ) = ">2 min", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 120
            && 'Table'[time_bucket] <= 2400
    ),
    SELECTEDVALUE ( Range[Range] ) = ">20 min", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] > 2400 
    ),
    SELECTEDVALUE ( Range[Range] ) = BLANK (), 
    SUM ( 'Table'[n] )
)

    3. Create the Matrix.

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • cewjr9842's avatar
      cewjr9842
      Frequent Visitor
      6 years ago

      I would first like to start by saying thank you, this hit the nail right on the head!