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
Anonymous
Not applicable

A matrix showing various metrics (text, percentage, integers etc)

Good morning from Australia

 

I had a need to show various 'summary' metrics on with various formats and with the data coming from multiple sources.

 

Initially I used text boxes and cards but to be honest it never looked very good (formatting).

 

What follows is what I did to get the visual as a matrix and displaying all the items (with a touch of conditional formatting).

 

The desired results are as follows;

 

I1.jpg

 

For the example The metric text is pure text, The second is text and to be formatted in Red, the third is an integer, the fourth is money and the final one is a percentage.

 

First step is to enter data to end up with a table called Metrics;

 

I2.jpg

 

Then we have our actual data (Although in my realworld solution I have data coming from a SQL Database and Dynamics 365) I am just using a simple flat file as the data source.

 

Here is the data (Tdata)

 

T3.jpg

 

Hmm - on my screen TPerc2 is slightly obscured but trust me - it has a value of 7!

 

Now we need to get the actual metric value into a common format (text) as follows via a new column on table Metrics;

 

(As an aside, I have yet to find a way of creating new lines in the DAX editor - so I normally cut/paste to Ultraedit when writing multi line DAX statements - if anyone has a method please let me know)

 

T4.jpg

At this stage it is all over bar the shouting. Create a new visual (Matrix) put the Metric and Metric value into the Matrix, turn of column totals and then conditionally format the two items in the matrix that should be red as follows;

 

T5.jpg

 

Note: My calculation of the percentage value is incorrect (needs a divide by 100).

 

Stan Gifford

CRM Solutions Pty Ltd

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

Thank you for your sharing, and you could also use a measure instead of it.

Measure =
IF (
    SELECTEDVALUE ( Metrics[Sequence] ) = 1,
    MAX ( Tdata[Tvalue] ),
    IF (
        SELECTEDVALUE ( Metrics[Sequence] ) = 2,
        MAX ( Tdata[Tredvalue] ),
        IF (
            SELECTEDVALUE ( Metrics[Sequence] ) = 3,
            FORMAT ( MAX ( Tdata[Tinteger] ), "General Number" ),
            IF (
                SELECTEDVALUE ( Metrics[Sequence] ) = 4,
                FORMAT ( MAX ( Tdata[Tmoney] ), "Currency" ),
                IF (
                    SELECTEDVALUE ( Metrics[Sequence] ) = 5,
                    FORMAT ( MAX ( Tdata[Tperc1] ) / MAX ( Tdata[Tperc2] ), "percent" ),
                    ""
                )
            )
        )
    )
)

and for As an aside, I have yet to find a way of creating new lines in the DAX editor - so I normally cut/paste to Ultraedit when writing multi line DAX statements - if anyone has a method please let me know

You could enter "Alt"+"Enter", that will create new lines in the DAX editor.

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

Thank you for your sharing, and you could also use a measure instead of it.

Measure =
IF (
    SELECTEDVALUE ( Metrics[Sequence] ) = 1,
    MAX ( Tdata[Tvalue] ),
    IF (
        SELECTEDVALUE ( Metrics[Sequence] ) = 2,
        MAX ( Tdata[Tredvalue] ),
        IF (
            SELECTEDVALUE ( Metrics[Sequence] ) = 3,
            FORMAT ( MAX ( Tdata[Tinteger] ), "General Number" ),
            IF (
                SELECTEDVALUE ( Metrics[Sequence] ) = 4,
                FORMAT ( MAX ( Tdata[Tmoney] ), "Currency" ),
                IF (
                    SELECTEDVALUE ( Metrics[Sequence] ) = 5,
                    FORMAT ( MAX ( Tdata[Tperc1] ) / MAX ( Tdata[Tperc2] ), "percent" ),
                    ""
                )
            )
        )
    )
)

and for As an aside, I have yet to find a way of creating new lines in the DAX editor - so I normally cut/paste to Ultraedit when writing multi line DAX statements - if anyone has a method please let me know

You could enter "Alt"+"Enter", that will create new lines in the DAX editor.

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you Lin - I will try the Alt Enter trick - will save a lot of fiddling about.

 

Your comments about the measure are taken - good point - Of course with S/W there are of course a zillion ways of doing something - I future I will probably use a measure!

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.