Forum Discussion

JG_0117's avatar
JG_0117
New Member
5 years ago
Solved

Returning Most Recurring Field per each Unique Value in Separate Column

Hello! I have a dataset that has a project number in one field and the project type in another. There are multiple project types per project number, however I would like to only display the most recurring project type for each specific project number. In the example below, 123456 has Labor occuring twice, Material once, and OH once. I would prefer that only Labor shows up for the project type. 

 

The below calculated column counts how many times each project type occurs per project #, however I'm not sure how I can utilize that to return the actual project type field instead of a value.

 

I would prefer to be able to do this in PowerQuery if possible considering how I'll be using the data. If not, how should this be done with calculated columns? 

 

Thank You!

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    JG_0117 

    Try the following:

    1) Number of project type rows per Proj Number

    Number Proj. Type = COUNTROWS(FactTable)

    2) Highest number of proj type rows per proj. number:

    Highest Project Type per Proj Number = 
VAR calc = MAXX(ALLEXCEPT(FactTable, FactTable[Project number]), [Number Proj. Type])
RETURN
IF([Number Proj. Type] = calc, [Number Proj. Type])

     

    And you can then use this last measure in your visual or as a filter in the filter pane:

     

  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, JG_0117 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Result = 
var tab = 
SUMMARIZE(
    FILTER(
       ALL('Table'),
       [Project Number]=MAX('Table'[Project Number])
    ),
    'Table'[Project Number],
    'Table'[Project Type],
    "Count",
    COUNTROWS('Table')
)
var m = 
MAXX(
    tab,
    [Count]
)
return
CONCATENATEX(
    FILTER(
        tab,
        [Count]=m
    ),
    [Project Type],
    ","
)

     

    Result:

     

    Best Regards

    Allan

     

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

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    JG_0117 

    Try the following:

    1) Number of project type rows per Proj Number

    Number Proj. Type = COUNTROWS(FactTable)

    2) Highest number of proj type rows per proj. number:

    Highest Project Type per Proj Number = 
VAR calc = MAXX(ALLEXCEPT(FactTable, FactTable[Project number]), [Number Proj. Type])
RETURN
IF([Number Proj. Type] = calc, [Number Proj. Type])

     

    And you can then use this last measure in your visual or as a filter in the filter pane:

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    JG_0117 what is the logic to pick the labour in your example because it has more rows? What happens if there is a tie? Your logic is not clear.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, JG_0117 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Result = 
var tab = 
SUMMARIZE(
    FILTER(
       ALL('Table'),
       [Project Number]=MAX('Table'[Project Number])
    ),
    'Table'[Project Number],
    'Table'[Project Type],
    "Count",
    COUNTROWS('Table')
)
var m = 
MAXX(
    tab,
    [Count]
)
return
CONCATENATEX(
    FILTER(
        tab,
        [Count]=m
    ),
    [Project Type],
    ","
)

     

    Result:

     

    Best Regards

    Allan

     

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