Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Syndicate_Admin
Administrator
Administrator

Calculate data in a column from a selection of data

I have a data table showing prices of different items in the local currency of each country, the date, the country, and the exchange rate of the local currency to US dollars, amount sold.

What I need is to have a report in which selecting, local currency or US dollars; The report presents me with the data in dollars or in the local currency.

The most obvious and easy thing is to have a column with the price in dollars (additional to the one in local currency), but that would lead me to have reports in both dollars and local currency; And that's tedious.

I hope you can help me.

1 ACCEPTED SOLUTION
v-yinliw-msft
Community Support
Community Support

Hi @Syndicate_Admin ,

 

You can try this method:

Sample data:

vyinliwmsft_0-1668764625259.png

 

Then you need to new a table to be a slicer:

vyinliwmsft_1-1668764625260.png

 

Then in the DataTable, new a measure:

USDORLOCAL =

var _USD = SUMX(SUMMARIZE('DataTable', 'DataTable'[Country],'DataTable'[Exchange Rate],'DataTable'[Price],'DataTable'[Items], "USD", 'DataTable'[Exchange Rate] * 'DataTable'[Price]),[USD])

var _Local = SUMX(SUMMARIZE('DataTable','DataTable'[Country],'DataTable'[Items],'DataTable'[Price]), 'DataTable'[Price])

return

IF(SELECTEDVALUE(SlicerTable[Slicer]) = "USD", _USD, _Local)

The result is:

vyinliwmsft_2-1668764625267.png

 

vyinliwmsft_3-1668764625276.png

 

Is this what you expect?

Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

View solution in original post

1 REPLY 1
v-yinliw-msft
Community Support
Community Support

Hi @Syndicate_Admin ,

 

You can try this method:

Sample data:

vyinliwmsft_0-1668764625259.png

 

Then you need to new a table to be a slicer:

vyinliwmsft_1-1668764625260.png

 

Then in the DataTable, new a measure:

USDORLOCAL =

var _USD = SUMX(SUMMARIZE('DataTable', 'DataTable'[Country],'DataTable'[Exchange Rate],'DataTable'[Price],'DataTable'[Items], "USD", 'DataTable'[Exchange Rate] * 'DataTable'[Price]),[USD])

var _Local = SUMX(SUMMARIZE('DataTable','DataTable'[Country],'DataTable'[Items],'DataTable'[Price]), 'DataTable'[Price])

return

IF(SELECTEDVALUE(SlicerTable[Slicer]) = "USD", _USD, _Local)

The result is:

vyinliwmsft_2-1668764625267.png

 

vyinliwmsft_3-1668764625276.png

 

Is this what you expect?

Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.