Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I would like to create a table where it shows a total value for a different criteria.
For example, my data looks like this;
Country Name | Original Currency | Original Currency Amount | Converted Currency to GBP |
America | USD | 1,000.00 | - |
Germany | EUR | 2,000.00 | 1,829.61 |
France | EUR | 1,500.00 | 1,372.21 |
My plan is to show a finalized table that looks like this;
Name | Report Amount |
America | 1,000.00 |
Germany | 1,829.61 |
France | 1,372.21 |
Please note that, for America, I want to show the original currency amount which is in USD 1,000.
For the other two, I want to show the converted currency to GBP.
I only know a way = CALCULATE(SUM(Original Currency), Original Currency="USD") but this will only show America.
Is there any other work around for this?
Thank you.
Solved! Go to Solution.
Hi @harizothman ,
You can use @FarhanAhmed 's method, the measure is this.
Report Amount1 =
IF (
MAX ( 'Table'[Country Name] ) = "America",
SUM ( 'Table'[Original Currency Amount] ),
SUM ( 'Table'[Converted Currency to GBP] )
)
Or you can use SWITCH function to create a measure.
Report Amount2 =
VAR OCA =
SUM ( 'Table'[Original Currency Amount] )
VAR GBP =
SUM ( 'Table'[Converted Currency to GBP] )
RETURN
SWITCH (
MAX ( 'Table'[Country Name] ),
"America", OCA,
"France", GBP,
"Germany", GBP
)
The result is this.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @harizothman ,
You can use @FarhanAhmed 's method, the measure is this.
Report Amount1 =
IF (
MAX ( 'Table'[Country Name] ) = "America",
SUM ( 'Table'[Original Currency Amount] ),
SUM ( 'Table'[Converted Currency to GBP] )
)
Or you can use SWITCH function to create a measure.
Report Amount2 =
VAR OCA =
SUM ( 'Table'[Original Currency Amount] )
VAR GBP =
SUM ( 'Table'[Converted Currency to GBP] )
RETURN
SWITCH (
MAX ( 'Table'[Country Name] ),
"America", OCA,
"France", GBP,
"Germany", GBP
)
The result is this.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @FarhanAhmed and @v-stephen-msft ,
yes, the solutions provided answered my queries.
thanks for the help!
= IF ( Original Currency="USD" , SUM(Original Currency), SUM(Converted Currency))
OR
= IF ( Country="America" , SUM(Original Currency), SUM(Converted Currency))
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |