Required help to create Price band in Sales Table based on selected currency in dashboard.

[Anonymous]

Hi, 

Currently I am working on Retail dataset which deals with multiple stores, sales etc. I have a requirement to create price band column in my table and use it as visual. 

Ex- My Sales table consist of 10 products and sales to it, then we need to create price band column next to it to define between which range exactly each product is. 

To get price band column, i have created one table & 2 measures. 

1. Price band step value Table - 

GENERATESERIES(10, 1000, 10)

2. Price Unit measure  - Sales amount/ Sales Quantity 

3. Price band fix = IF(MOD([Price Unit],'Price band step'[Price band step Value])==0,[Price Unit]+1,[Price Unit])

Last I created Price range calculated column - 

CONCATENATE(FORMAT(ROUNDUP( [Price band fix]/ 'Price band step'[Price band step Value],0),"00 : "), CONCATENATE(CONCATENATE(ROUNDDOWN( [Price band fix]/ 'Price band step'[Price band step Value],0)*'Price band step'[Price band step Value]," - "),ROUNDUP( [Price band fix]/ 'Price band step'[Price band step Value],0)*'Price band step'[Price band step Value]))

Now I need this price range calculated column to change based on currency selection in my report. If end user selects USD, price range for all the countries product shoudl display price range in USD. 

Any suggestion how to achieve that? 

Using currency Table - 

Sales Table - 

Whatever price range in current table is in country local currency, that means thailand sales, price everything is in Thailand local currency - THB, same applies to Vietnam its in VND. Now my requirement is to make that price band change to watever currency we select in report. If we select THB, all the country product sales, price & price range should change to THB. 

Could anyone suggest some solution for this? 

[v-zhangti]

Hi, @Anonymous 

I'm having trouble with the Price band fix measure when restoring your data.

Please help to correct this formula.

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-zhangti,

Thanks for your response. 

Date	Country	Store id	Product Code	Sales net	Sales unit	Price	Price Band
30-12-2021	Thailand	5432	ET11HF	2500	2	1250	13:1200-1300
01-02-2021	Thailand	7654	FR23FUI	3000	1	3000	30:2900-3000
03-02-2021	Thailand	5673	ER43GJF	1500	3	500	05:400-500
05-06-2021	Thailand	6574	TYF3452	4648	1	4648	47:4600-4700
21-11-2021	Thailand	9873	GR5647	5358	4	1339.5	14:1300-1400
16-05-2021	Thailand	7875	FD14536	8579	2	4289.5	43:4200-4300
17-07-2021	Thailand	8289	HFG785	8613	4	2153.25	22:2100-2200
14-11-2021	Thailand	3648	HIH9832	7354	2	3677	37:3600-3700
01-10-2021	Thailand	3647	BGS7657	6453	4	1613.25	17:1600-1700
11-11-2021	Thailand	4324	JDF4272	8700	2	4350	44:4300-4400
30-11-2021	Thailand	5798	KRD7598	5317	4	1329.25	14:1300-1400

I have shared sample sales data with just 10 rows. Based on the price band fix measure, I can get Price range column which defines range for each product. 

Price band step Value = SELECTEDVALUE('Price band step'[Price band step],100)

First,I have created Price band step value measure and then used in price band fix measure.

Price band fix = IF(MOD([Price Unit],'Price band step'[Price band step Value])==0,[Price Unit]+1,[Price Unit])

Expected output is price range column should change according to the currency selection in my dashboard.

As of now, My sales, Price measures are changing according to currency selection, but price range remains same even though if i select any other currency. 

Regards, 

Hkrishna_1234.