Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Top N values for each category after separating column

Hi, 

I am hoping to get some help on this!

 

The image below presents different locations for each category.

I am trying to have the Locations and Prices for "Alaska" and "Florida" in separate columns than the other Locations and Prices. Then, for each category, I want the Top 2 prices for the "Alaska" and "Florida" column, and the top 2 prices for the other locations column.

 

In the image below (this may be the wrong apprach), I attempted to separate the locations and prices, but the nulls are in the way. 

The values should start at the top while referencing the correct categories. Then, I need to have the top 2 prices for each of the pricing columns.

 

Attaching the table.

ProductGroupLocationPrice
AAA55Alaska0.440
AAA55Texas0.342
AAA32Florida0.875
AAA32Delaware0.486
AAA32Indiana0.772
AAA32Alaska0.234
AAA32Maine0.959
BBB6Florida0.358
BBB6Delaware0.938
BBB43Alaska0.759
BBB43Ohio0.375
CCC9Alaska0.234
CCC9Florida0.589
CCC9Maine0.739
CCC12Alaska0.275
CCC12Texas0.384

 

(Side note: My real data is not separated by states. They are just sample locations here.)

Sorry if anything is confusing. Please do ask any clarification questions. Thank you in advance!

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    Base data:

    Output     result:

     

    Try the below steps:

    1.Add new column on the base table:

    newcolumn = IF('Table'[Location]="Alaska"||'Table'[Location]="Florida","Type1","Type2")
    rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Product] = EARLIER ( 'Table'[Product] )
            && 'Table'[Group] = EARLIER ( 'Table'[Group] )
            && 'Table'[newcolumn] = EARLIER ( 'Table'[newcolumn] )
    ),
    'Table'[Price],
    ,
    DESC,
    DENSE
)

     

    Then create the below two new table:

    Table2 = 
FILTER (
    SELECTCOLUMNS (
        'Table',
        "Product", CALCULATE ( VALUES ( 'Table'[Product] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2 ),
        "Group", CALCULATE ( VALUES ( 'Table'[Group] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2 ),
        "Location1", CALCULATE ( VALUES ( 'Table'[Location] ), 'Table'[newcolumn] = "Type1" &&'Table'[rank]<=2),
        "Price1", CALCULATE ( VALUES ( 'Table'[Price] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2),
        "Rank", 'Table'[rank]
    ),
    NOT ( ISBLANK ( [Product] ) )
)
    Table 3 =
FILTER (
    SELECTCOLUMNS (
        'Table',
        "Product", CALCULATE ( VALUES ( 'Table'[Product] ), 'Table'[newcolumn] = "Type2" ),
        "Group", CALCULATE ( VALUES ( 'Table'[Group] ), 'Table'[newcolumn] = "Type2" ),
        "Location2", CALCULATE ( VALUES ( 'Table'[Location] ), 'Table'[newcolumn] = "Type2" ),
        "Price2", CALCULATE ( VALUES ( 'Table'[Price] ), 'Table'[newcolumn] = "Type2" ),
        "rank", 'Table'[rank]
    ),
    NOT ( ISBLANK ( [Product] ) )
)

     Then add new column on table2：

    location2 = LOOKUPVALUE('Table 3'[Location2],'Table 3'[Product],Table2[Product],'Table 3'[Group],Table2[Group],'Table 3'[rank],Table2[Rank])
    price2 = LOOKUPVALUE('Table 3'[Price2],'Table 3'[Product],Table2[Product],'Table 3'[Group],Table2[Group],'Table 3'[rank],Table2[Rank])

     

    Finally, adjust the order of table 2 below (I deleted the rank in the creation process, so that the table reported an error, and then re-entered the creation statement, the order was automatically adjusted).

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you! Which columns would I keep pivoted and which columns do I unpivot?

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    Base data:

    Output     result:

     

    Try the below steps:

    1.Add new column on the base table:

    newcolumn = IF('Table'[Location]="Alaska"||'Table'[Location]="Florida","Type1","Type2")
    rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Product] = EARLIER ( 'Table'[Product] )
            && 'Table'[Group] = EARLIER ( 'Table'[Group] )
            && 'Table'[newcolumn] = EARLIER ( 'Table'[newcolumn] )
    ),
    'Table'[Price],
    ,
    DESC,
    DENSE
)

     

    Then create the below two new table:

    Table2 = 
FILTER (
    SELECTCOLUMNS (
        'Table',
        "Product", CALCULATE ( VALUES ( 'Table'[Product] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2 ),
        "Group", CALCULATE ( VALUES ( 'Table'[Group] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2 ),
        "Location1", CALCULATE ( VALUES ( 'Table'[Location] ), 'Table'[newcolumn] = "Type1" &&'Table'[rank]<=2),
        "Price1", CALCULATE ( VALUES ( 'Table'[Price] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2),
        "Rank", 'Table'[rank]
    ),
    NOT ( ISBLANK ( [Product] ) )
)
    Table 3 =
FILTER (
    SELECTCOLUMNS (
        'Table',
        "Product", CALCULATE ( VALUES ( 'Table'[Product] ), 'Table'[newcolumn] = "Type2" ),
        "Group", CALCULATE ( VALUES ( 'Table'[Group] ), 'Table'[newcolumn] = "Type2" ),
        "Location2", CALCULATE ( VALUES ( 'Table'[Location] ), 'Table'[newcolumn] = "Type2" ),
        "Price2", CALCULATE ( VALUES ( 'Table'[Price] ), 'Table'[newcolumn] = "Type2" ),
        "rank", 'Table'[rank]
    ),
    NOT ( ISBLANK ( [Product] ) )
)

     Then add new column on table2：

    location2 = LOOKUPVALUE('Table 3'[Location2],'Table 3'[Product],Table2[Product],'Table 3'[Group],Table2[Group],'Table 3'[rank],Table2[Rank])
    price2 = LOOKUPVALUE('Table 3'[Price2],'Table 3'[Product],Table2[Product],'Table 3'[Group],Table2[Group],'Table 3'[rank],Table2[Rank])

     

    Finally, adjust the order of table 2 below (I deleted the rank in the creation process, so that the table reported an error, and then re-entered the creation statement, the order was automatically adjusted).

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien