cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
apmehta Frequent Visitor
Frequent Visitor

show values against static column and group it

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:Capture1.PNG

 

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.Capture2.PNG

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:

Capture3.PNG

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).

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: show values against static column and group it

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Super User
Super User

Re: show values against static column and group it

HI @apmehta

 

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

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
8 REPLIES 8
Super User
Super User

Re: show values against static column and group it

Hi @apmehta

 

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

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
apmehta Frequent Visitor
Frequent Visitor

Re: show values against static column and group it

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

Super User
Super User

Re: show values against static column and group it

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 LimitSkid Resistence / GripNumber of LanesLane WidthPaved 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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
apmehta Frequent Visitor
Frequent Visitor

Re: show values against static column and group it

Any leads anyone ?

Super User
Super User

Re: show values against static column and group it

Hi @apmehta

 

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

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Super User
Super User

Re: show values against static column and group it

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
apmehta Frequent Visitor
Frequent Visitor

Re: show values against static column and group it

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) ?

Super User
Super User

Re: show values against static column and group it

HI @apmehta

 

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

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!