Forum Discussion
SWITCH or VAR Function for multi-data types in single table for rows and columns?
Hey thanks, I actually figured it out on my own after some trial and error....I'll explain Icey . Your proposed solution was close, but I had essentially already done the exact same thing before I post, but it doesn't consider different data formats for Revenue or EBITDA by Financial Type, i.e., $ and % on the same row as well as the same column.
But here's the fix:
Initially, I had created simple, calculated measures for Revenue, EBITDA and Margin %. Then I created aliases or Data Formatted Measures using each of those three original measures for every Financial Type Sort Order ID that represented all of the Financial Types (15 in total): Revenue Formatted, EBITDA Formatted and Margin Formatted. So, basically, the alias would change the data format to % or $ of the original measure based on the Financial Type's Sort Order ID, i.e 1 = $, 2 = %, 3 = $, et al..
E.g.,
Revenue Formatted =SWITCH(VALUES('Financial Types'[Financial Types Sort Order]),1, FORMAT([Revenue], "0.00%") -- first column header is a revenue percentage, so this changes the decimal data type to text w/ %,2, FORMAT([Revenue], "0.00%"),3, FORMAT([Revenue], "$0,0") -- third column header is a whole dolalr amount, so this changes the decimal data type to text w/ $,4, FORMAT([Revenue], "$0,0"),5, FORMAT([Revenue], "$0,0"),6, FORMAT([Revenue], "0.00%"),7, FORMAT([Revenue], "0.00%"),8, FORMAT([Revenue], "$0,0"),9, FORMAT([Revenue], "$0,0"),10, FORMAT([Revenue], "0.00%"),11, FORMAT([Revenue], "0.00%"),12, FORMAT([Revenue], "$0,0"),13, FORMAT([Revenue], "0.00%"),14, FORMAT([Revenue], "$0,0"),15, FORMAT([Revenue], "$0,0"))Then, I used another SWITCH(VALUES)/FORMAT function to combine/consolidate all three Formatted Measures into one single Measure I used as the "Value" in my table matrix, and it worked perfectly, but I'm sure there's a cleaner way to accomplish all of this.
I did the following:
Selected Measure = MAX('Financial Categories'[Sort Order ID])Switch Measure =SWITCH([Selected Measure],1,FORMAT([Revenue Formatted], [Revenue Formatted]),2,FORMAT([EBITDA Formatted], [EBITDA Formatted]),3,FORMAT([Margin Formatted], [Margin Formatted]) )I dropped the 'Switch Measure' into the Values of the Matrix table and boom, it worked flawlessly...here's a screen grab of what it looks like now:
Again, for a static, monthly dashboard with minimal, simple applied slicers, this works, I just don't know how sustainable it is for more complex or ambitious BI dev work.
Any advice or critiques would be appreciated!
Hi Anonymous ,
My understanding is that you want to show different data type in the same column. If my understanding is correct, please continue to look on, if my understanding is wrong, please let me know and provide the relevant sample data, preferably in the form of a screenshot or chart.
1. My sample data is as below.
2. You can write your measure like so.
Revenue =
SWITCH (
MAX ( 'Table'[Financial categories] ),
"Revenue", FORMAT ( MAX ( 'Table'[Values] ), "$00" ),
"EBITDA", FORMAT ( MAX ( 'Table'[Values] ), "$00" ),
"Margin", FORMAT ( MAX ( 'Table'[Values] ), "0.00%" )
)
3. Put the measure into matrix.
You can check more details from here.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey thanks, I actually figured it out on my own after some trial and error....I'll explain Icey . Your proposed solution was close, but I had essentially already done the exact same thing before I post, but it doesn't consider different data formats for Revenue or EBITDA by Financial Type, i.e., $ and % on the same row as well as the same column.
But here's the fix:
Initially, I had created simple, calculated measures for Revenue, EBITDA and Margin %. Then I created aliases or Data Formatted Measures using each of those three original measures for every Financial Type Sort Order ID that represented all of the Financial Types (15 in total): Revenue Formatted, EBITDA Formatted and Margin Formatted. So, basically, the alias would change the data format to % or $ of the original measure based on the Financial Type's Sort Order ID, i.e 1 = $, 2 = %, 3 = $, et al..
E.g.,
Revenue Formatted =SWITCH(VALUES('Financial Types'[Financial Types Sort Order]),1, FORMAT([Revenue], "0.00%") -- first column header is a revenue percentage, so this changes the decimal data type to text w/ %,2, FORMAT([Revenue], "0.00%"),3, FORMAT([Revenue], "$0,0") -- third column header is a whole dolalr amount, so this changes the decimal data type to text w/ $,4, FORMAT([Revenue], "$0,0"),5, FORMAT([Revenue], "$0,0"),6, FORMAT([Revenue], "0.00%"),7, FORMAT([Revenue], "0.00%"),8, FORMAT([Revenue], "$0,0"),9, FORMAT([Revenue], "$0,0"),10, FORMAT([Revenue], "0.00%"),11, FORMAT([Revenue], "0.00%"),12, FORMAT([Revenue], "$0,0"),13, FORMAT([Revenue], "0.00%"),14, FORMAT([Revenue], "$0,0"),15, FORMAT([Revenue], "$0,0"))Then, I used another SWITCH(VALUES)/FORMAT function to combine/consolidate all three Formatted Measures into one single Measure I used as the "Value" in my table matrix, and it worked perfectly, but I'm sure there's a cleaner way to accomplish all of this.
I did the following:
Selected Measure = MAX('Financial Categories'[Sort Order ID])Switch Measure =SWITCH([Selected Measure],1,FORMAT([Revenue Formatted], [Revenue Formatted]),2,FORMAT([EBITDA Formatted], [EBITDA Formatted]),3,FORMAT([Margin Formatted], [Margin Formatted]) )I dropped the 'Switch Measure' into the Values of the Matrix table and boom, it worked flawlessly...here's a screen grab of what it looks like now:
Again, for a static, monthly dashboard with minimal, simple applied slicers, this works, I just don't know how sustainable it is for more complex or ambitious BI dev work.
Any advice or critiques would be appreciated!