Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
qwe23
Frequent Visitor

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.

Capture2.PNG

 

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.

Capture.PNG

 

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!

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @qwe23 ,

Base data:

vluwangmsft_0-1656483812638.png

Output     result:

vluwangmsft_1-1656483889956.png

 

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

 

vluwangmsft_2-1656486248969.png

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


Best Regards

Lucien

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @qwe23 ,

Base data:

vluwangmsft_0-1656483812638.png

Output     result:

vluwangmsft_1-1656483889956.png

 

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

 

vluwangmsft_2-1656486248969.png

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


Best Regards

Lucien

amitchandak
Super User
Super User

@qwe23 , You can use unpivot columns in power query

 

https://radacad.com/pivot-and-unpivot-with-power-bi

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.