Forum Discussion
filter by another columns text
I think this will work:
Country Profit = SUMX( FILTER( 'Europe Profit', var CurrencyCode = IF( MAX('Europe Profit'[Country by Business]) = "Poland", "EUR", "Local" ) RETURN 'Europe Profit'[Reporting Currency] = CurrencyCode ), 'Europe Profit'[Conv. Rate] )
edhans Yes this is what i wanted. Small difference is that I want it without the column of Reporting Currency.
but I dont want to built a new table just to take that. I need a measure that will filter both columns and it will give the proper value.
Reporting Currency Amount =
VAR Country = MAX('Table'[Country by Business])
VAR CurrencyCode =
IF(
Country = "Poland",
"EUR",
"Local"
)
RETURN
CALCULATE(
MAX('Table'[Amount]),
FILTER(
'Table',
'Table'[Reporting Currency] = CurrencyCode
)
)hi edhans
Yes this can work but I have more than 1 lines to sum on the column of the values. in this formula it only returns the Max value of the country.
So the question is, how I should tell it to gather all the values and sum them?
Example.
Conv. Rate Date Reporting Currency Country by Business 1.66 2/1/2020 0:00 EUR Poland 11.82 2/1/2020 0:00 EUR Poland 387.83 2/1/2020 0:00 EUR Poland I'm going to ask again. Show your data you have now, in a table format. Then show some screenshots or output of what you expect. This thread is now 7-8 deep and this is the first time you've asked for a SUM. Help me help you. Provide a clear and complete explanation of what you are looking for.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumHi edhans I am sorry for all the misunderstood and once again thank you for all your support on this! 🙂
Look the below table is the info that I have:
Accounts Conv. Rate Reporting Currency Country by Business 1. Gross Profit 7.15 Local Poland 1. Gross Profit 51.01 Local Poland 1. Gross Profit 1,672.99 Local Poland 1. Gross Profit 11.82 EUR Poland 1. Gross Profit 387.83 EUR Poland 1. Gross Profit 45.25 EUR Poland 1. Gross Profit 216.14 Local Sweden 1. Gross Profit 5.86 Local Sweden 1. Gross Profit 220.46 Local Sweden 1. Gross Profit 1,761.57 Local Sweden 1. Gross Profit 20.29 EUR Sweden 1. Gross Profit 0.55 EUR Sweden 1. Gross Profit 20.70 EUR Sweden 1. Gross Profit 165.41 EUR Sweden And here is what I want to see.
Country by Business Conv. Rate Poland 444.9 Sweden 2204.02 For Poland is the sum of EUR and for Sweden (and rest of countries that I have) Local reporting currency.
Let me know if you need further info.
Cheers,