Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Product | Group | Location | Price |
AAA | 55 | Alaska | 0.440 |
AAA | 55 | Texas | 0.342 |
AAA | 32 | Florida | 0.875 |
AAA | 32 | Delaware | 0.486 |
AAA | 32 | Indiana | 0.772 |
AAA | 32 | Alaska | 0.234 |
AAA | 32 | Maine | 0.959 |
BBB | 6 | Florida | 0.358 |
BBB | 6 | Delaware | 0.938 |
BBB | 43 | Alaska | 0.759 |
BBB | 43 | Ohio | 0.375 |
CCC | 9 | Alaska | 0.234 |
CCC | 9 | Florida | 0.589 |
CCC | 9 | Maine | 0.739 |
CCC | 12 | Alaska | 0.275 |
CCC | 12 | Texas | 0.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!
Solved! Go to Solution.
Hi @qwe23 ,
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
Hi @qwe23 ,
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
@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?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |