How to Pivot using multiple columns

[DTHOMSON198409]

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 

[Anonymous · Accepted solution]

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