Forum Discussion

DTHOMSON198409's avatar
DTHOMSON198409
New Member
2 years ago
Solved

How to Pivot using multiple columns

HI,

 

I am in the middle of creating a dashboard from the below Data 

 

 

I have been tasked with providing something along the lines of the below where for each DM_CODE FOR each day and price point they would see the difference in prices between each price source, so the user can determine which price is the best or if they are all the same ?

 

Any help would be appreciated 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DTHOMSON198409 ,

    The Table data is shown below:

    Please follow these steps:

    1.Adding indexed columns after grouping with Power Query

    2.Delete column 'Count', expand column 'Coustom'

    3.Use the following DAX expression to create columns

     

    bid_price = 
VAR _a = [DM_CODE]
VAR _b = [price_point]
RETURN CONCATENATEX(FILTER('Table','Table'[DM_CODE] = _a && 'Table'[price_point] = _b),[Custom.bid_price],",")
    mid_price = 
VAR _a = [DM_CODE]
VAR _b = [price_point]
RETURN CONCATENATEX(FILTER('Table','Table'[DM_CODE] = _a && 'Table'[price_point] = _b),[Custom.mid_price],",")

     

    4.Copy the table data to Excel and reopen it using Power Query. Then click on 'Split Columns'

    5.Use the following DAX expression to create a measure

     

    Difference_price.1_price.2 = 
VAR _a = SELECTEDVALUE('Table'[bid_price.1])
VAR _b = SELECTEDVALUE('Table'[bid_price.2])
RETURN IF(ISBLANK(_a),-1,IF(_a = _b,0,DIVIDE(_a - _b,_b,1)))

     

    6.Final output

     

     

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DTHOMSON198409 ,

    The Table data is shown below:

    Please follow these steps:

    1.Adding indexed columns after grouping with Power Query

    2.Delete column 'Count', expand column 'Coustom'

    3.Use the following DAX expression to create columns

     

    bid_price = 
VAR _a = [DM_CODE]
VAR _b = [price_point]
RETURN CONCATENATEX(FILTER('Table','Table'[DM_CODE] = _a && 'Table'[price_point] = _b),[Custom.bid_price],",")
    mid_price = 
VAR _a = [DM_CODE]
VAR _b = [price_point]
RETURN CONCATENATEX(FILTER('Table','Table'[DM_CODE] = _a && 'Table'[price_point] = _b),[Custom.mid_price],",")

     

    4.Copy the table data to Excel and reopen it using Power Query. Then click on 'Split Columns'

    5.Use the following DAX expression to create a measure

     

    Difference_price.1_price.2 = 
VAR _a = SELECTEDVALUE('Table'[bid_price.1])
VAR _b = SELECTEDVALUE('Table'[bid_price.2])
RETURN IF(ISBLANK(_a),-1,IF(_a = _b,0,DIVIDE(_a - _b,_b,1)))

     

    6.Final output