SWITCH or VAR Function for multi-data types in single table for rows and columns?

[williamadams12]

Hey there - I have a data table where I need to consolidate multiple data types, both whole numbers in large dollar amounts and also percents all on the same row and/or column. The data type is set to decimal, and I'm using some switch logic to change the data type based on the measure category i created. E.g, Revenue = $, Margin = %, but I also need the Revenue measure to switch to percent if it's associated with a specific category...as you can see below the two categories are 20205+7 vs. 20203+9 and 20205+7 vs. 2019A. 

E.g., the R, E and M categories are measures in a switch function based on a data table that has field headers which look like this: 

region, subregion, financial category, financial type, scenario, amount

So, i spread out the financial types as columns and then created a switch measure based on other created measures for revenue, ebitda and the margin, which almost works, except i still need additional logic in my revenue measure so that it knows to switch to percent if a revenue amount is associated with a specific type of financial category so it's displayed as a percent and not a dollar total as in the table below. 

Any ideas or helpful guidance would be appreciated! 

[Icey]

Hi @williamadams12 ,

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.

[williamadams12 · Accepted solution]

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! 

[amitchandak]

@williamadams12 , Not very clear to me.

One is:- show on row In Matrix -

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

the second same measure has a different format

This one is for a different purpose, but it can help

https://www.youtube.com/watch?v=vlnx7QUVYME

[lbendlin]

in your matrix visual put all the individual measures into the Values sink and then in the visual settings choose "Show on rows".