Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
williamadams12
Resolver I
Resolver I

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

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. 

 

williamadams12_0-1597177475295.png

 

Any ideas or helpful guidance would be appreciated! 

 

 

1 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: 

 

multi-data table v2.PNG

 

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! 

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

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.

sample data.png

 

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.

matrix visual.png

 

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: 

 

multi-data table v2.PNG

 

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
Super User
Super User

@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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.