The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Support,
I'm creating a Power BI report to consolidate the G/L Account data from Business Central.
I'm using the Cronus demo database with 3 companies, CANADA, MEXICO and USA.
For example, if I own 100% of the Canada company, but only own 50% of the MEXICO and 50% of the USA company.
I would like when I multiple select the companies to consolidate from the slicer, it will calculate according to the consolidation percentage above and shows the data in the Matrix visual with the individual company listed.
My issue is that I want to create a measure with the SWITCH statement to calculate this. But I do not get the result I want to have the G/L account data listed under the companies I selected and take consideration into the consolidation percentage.
The attache screenshot is the report I created. I created the calculated meausure Country Amount to calculate the amount for different countries.
I also created below measures to calculate the amount for each country.
Jessie.
Solved! Go to Solution.
Hi ibarrau,
Thank you for the reply.
I made it work by using the Amount in the Matrix visual, then drag the Country Amount to the same Matrix to compare the consoliated amount with the original amount. Amount is the column summed from the original table.
Somehow the Switch statement works when I select the multiple countries in the slicer by doing it this way.
Jessie.
Hi, before I write the mesure let me clarify that SWITCH will only comparte for only one value. If you click two items of the slicer it will go through "else" path. Consider that the first argument of the switch is the equal condition to check.
SWITCH ( SELECTEDVALUE ( 'Table'[Country] ) ; "Mexico"; [Measure calculation for Mexico] ; "Canada"; [Measure calculation for Canada] ; "USA"; [Measure calculation for USA] ; [Else path calculation] )
If you need to specificaly select two countries, then you can't use this swtich functions and you should build your own IF statements validating the combination of the selection.
IF ( AND ( "Canada" IN VALUES('Table'[Country]) && "Mexico" IN VALUES('Table'[Country]) ; COUNTROWS ( VALUES ('Table'[Country]) ) = 2 ) ; "True calculation" ; IF ( AND ( ... //other combination )
//final else )
That should work for multiple combinations.
Hope this helps,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!
Hi ibarrau,
Thank you for the reply.
I made it work by using the Amount in the Matrix visual, then drag the Country Amount to the same Matrix to compare the consoliated amount with the original amount. Amount is the column summed from the original table.
Somehow the Switch statement works when I select the multiple countries in the slicer by doing it this way.
Jessie.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
93 | |
88 | |
70 | |
65 |
User | Count |
---|---|
232 | |
128 | |
116 | |
82 | |
82 |