show values against static column and group it

[apmehta]

Hi All,

Fairly new to powerbi world so be kind, lol 😉 Also, will try to explain the scenario in its completeness and hoping that I m clear enough. will put screenshots as well to better suit my case:

Situation - 

I want to show a table (then a graph) in the following format:

Let me explain the workings now, Col CategoryID has static value from 1 - 19. On how it is determined that values of other col corresponds to which row no. is that each of the col (speed limit / Grip / Lanes) holds values between 1 - 19 in them.

Now the values you see under each of those cols (0.00 / 0.600 and so on) are actually sum of another field called SegmentValues.

Now if you see from from first picture the category ID corresponds to the values as shown under cols from 2nd pic. So like that I have 19 numbers and corresponding segment values against them. Hence what I want to show is jst the total against its respective column. That is 

for speed limit - under value 9 i have 6 segment values of 0.1 and hence i want to show 0.6 (sum of no. of instances of number 9) against ROW 9 in the main table (static table of 1 - 19).

Hope that makes sense.

Now 

I have reached to the following stage in powerBI:

I have 345,000 rows in total in my source data.

I created a custom col CategoryIDs via queryEditor - listing number from 1 to 345k.

As you see from the above table, my segment values for speed limit (9) shows on row 82635 - 82640, i guess thats why its showing on the table against those rows.

HOW-

How do i show static table of Category IDs from 1 -19 only. And get to plot the sum to segment value against the number found in speed limit col.

in this case 0.6 on row 9, as per original table (picutre 1).

Please advise.

(using latest version of powerbi).

[CheenuSing · Accepted solution]

Hi @apmehta

Try the following steps.

1. Create a Summary table as follows

Summary =
Union
(
   Summarize(Data,Data[Class_Category_Volume_Name_RAW],Data[Speed limit],"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","SpeedLimit","Order",1)
,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Skid resistance / grip]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Skid resistance / grip","Order",2)
 ,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Number of lanes]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Number of Lanes","Order", 3)
 ,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Lane Width]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Lane Width","Order", 4)
,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Paved shoulder - driver-side]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Paved shoulder - driver-side","Order", 5)
 ,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Paved shoulder - passenger-side]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Paved shoulder - passendger-side","Order", 6)
)

2. I added only six columns of your sumif sheet in the excel data file. You have to add the rest in similar manner.

3. Rename the column name Speed limit as  ID. Rename the column Class_Category_Volume_Name_RAW as Class_Category

4. Set the column Category to sort by Order, this is to match your SumIf sheet in the excel.

5. Create slicer using Class_Category

6. Create a Matrix Preview table with

    ID as Row,

    Category as Column

    Segment Total as Values.

If this works please accept it as a solution and also give KUDOS.

Cheers

CheeuSing

[apmehta]

Any leads anyone ?

[CheenuSing]

Hi @apmehta

I have sent you the solution in a private message.  Please check.

Cheers

CheenuSing

[CheenuSing · Accepted solution]

Hi @apmehta

Try the following steps.

1. Create a Summary table as follows

Summary =
Union
(
   Summarize(Data,Data[Class_Category_Volume_Name_RAW],Data[Speed limit],"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","SpeedLimit","Order",1)
,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Skid resistance / grip]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Skid resistance / grip","Order",2)
 ,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Number of lanes]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Number of Lanes","Order", 3)
 ,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Lane Width]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Lane Width","Order", 4)
,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Paved shoulder - driver-side]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Paved shoulder - driver-side","Order", 5)
 ,Summarize(Data,Data[Class_Category_Volume_Name_RAW],(Data[Paved shoulder - passenger-side]),"Segment Total",sum(Data[SEGMENT_LENGTH]),"Category","Paved shoulder - passendger-side","Order", 6)
)

2. I added only six columns of your sumif sheet in the excel data file. You have to add the rest in similar manner.

3. Rename the column name Speed limit as  ID. Rename the column Class_Category_Volume_Name_RAW as Class_Category

4. Set the column Category to sort by Order, this is to match your SumIf sheet in the excel.

5. Create slicer using Class_Category

6. Create a Matrix Preview table with

    ID as Row,

    Category as Column

    Segment Total as Values.

If this works please accept it as a solution and also give KUDOS.

Cheers

CheeuSing

[apmehta]

HI Cheenu,

Is there any way i can wrap the column headers ? or show all columns with full names rather than getting squished and not displaying in completeness ?

Is there no control on playing with column headers (formatting wise) ?

[CheenuSing · Accepted solution]

HI @apmehta

Use the matrix preview and go through the formatting options for column headers and you can do word wrap.

Cheers

CheenuSing

[CheenuSing]

Hi @apmehta

Is it possible share your data on one-drive and provide the link to find a solution.

Cheers

CheenuSing

[apmehta]

The Link to the file is as below :

It has 100k rows and is in XLSB format, can be coverted to csv format.https://1drv.ms/x/s!AgoxQDnQYaWhgU6AQSdH8u-Dh6z4

[CheenuSing]

Hi @apmehta

Which is the column for CategoryID in the source table.  

What is the business rule for computing the sum of SEGMENT_LENGTH under each of the columns below and CategoryID.

Speed Limit

Skid Resistence / Grip

Number of Lanes

Lane Width

Paved shoulder - driver-side

You can sendd me a private message until a solution is found, which can then be published in the community.

Cheers

CheenuSing