Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group values from field with condition

Dear users,

 

I have a table with :

Vehicule - Line 

d4564564   -   M   

d7164564   -   L    

d4544575   -   P    

d4747422   -   M  

d4887564   -   O  

d4564564   -   M  

d4564564   -   L   

 

I want to display those values in a bar chart by group some lines values, like this :

Line - Count Vehicule

L = 2

M = 3

O = 1

P = 1

M-O = 4 

L-P = 3

 

=> How can I group like this ?

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    First create 2 tables as below:

     

    Table 1 = 
ADDCOLUMNS('Table',"line2",'Table'[Line ])
    Table 2 = CROSSJOIN(DISTINCT('Table'[Line ]),DISTINCT('Table 1'[line2]))

     

    Then you will get all the combinations of the line;

    Create a calculated column as below:

     

    Line = IF('Table 2'[Line ]='Table 2'[line2],'Table 2'[Line ],CONCATENATE('Table 2'[Line ]&"-",'Table 2'[line2]))

     

    And a measure as below:

     

    Count Vehicule = IF(MAX('Table 2'[Line]) in FILTERS('Table'[Line ]),COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[Line])),'Table'[Line ]),COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[Line ])),'Table'[Line ])+COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[line2])),'Table'[Line ]))

     

    Finally you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

4 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    You can achieve this as follows:

    1. Create a Clustered Bar chart in Power BI
    2. Drag "LINE" column to Axis
    3. Move COUNT of "VEHICLE" column in Values option.

    Do you need grouping like "M-O" and "L-P" along with the existing values in "LINE" column?

     

     

    Thanks,

    Pragati

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

     

    Maybe you can transform the table.

    Table 3 = 
VAR TBL1= SUMMARIZE('Table (3)','Table (3)'[line],"count vehicule",count('Table (3)'[vehicule]))
VAR TBL2= SUMMARIZE('Table (3)',"line","M-O","count vehicule",COUNTX(FILTER('Table (3)','Table (3)'[line]="M" ||'Table (3)'[line]="O"), 'Table (3)'[vehicule]))
VAR TBL3=SUMMARIZE('Table (3)',"line","L-P","count vehicule",COUNTX(FILTER('Table (3)','Table (3)'[line]="L" ||'Table (3)'[line]="P"), 'Table (3)'[vehicule]))
RETURN UNION(TBL1,TBL2,TBL3)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    First create 2 tables as below:

     

    Table 1 = 
ADDCOLUMNS('Table',"line2",'Table'[Line ])
    Table 2 = CROSSJOIN(DISTINCT('Table'[Line ]),DISTINCT('Table 1'[line2]))

     

    Then you will get all the combinations of the line;

    Create a calculated column as below:

     

    Line = IF('Table 2'[Line ]='Table 2'[line2],'Table 2'[Line ],CONCATENATE('Table 2'[Line ]&"-",'Table 2'[line2]))

     

    And a measure as below:

     

    Count Vehicule = IF(MAX('Table 2'[Line]) in FILTERS('Table'[Line ]),COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[Line])),'Table'[Line ]),COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[Line ])),'Table'[Line ])+COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[line2])),'Table'[Line ]))

     

    Finally you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!