Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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;
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;
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)
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)
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;
Note: My calculation of the percentage value is incorrect (needs a divide by 100).
Stan Gifford
CRM Solutions Pty Ltd
Solved! Go to Solution.
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |