Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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