So today I recevied a question on the SML group which looked like the perfect use case for a calculation group. Somebody was trying to build a matrix and wanted to have the values without decimals but the totals with decimals. In other words the goal was defining different formats for values and totals. Without calculation groups, the only way you can get close to that is by using the function FORMAT, but that’s a dirty trick that will come back and get you because now your values are text. If you later use that measure in a SUMX expression for instance, it will not recognize it’s a number anymore. Of course you could keep a measure for the value and a different measure for the formatted value, but hmmm then you would have to do that for all measures that you might want to use this way and well, you would have to manage this sprawling number of measures. Let’s check both ways in case someone has external tools blocked or something.
Ok, this is one of those things that I might not do in production unless I have the CEO shouting on my ear «I want the bars of the matrix to be smaller so I can read the numbers, and no, you may not use a custom visual for that!». I know that might be quite a narrow use case, but hey, if the CEO wants it, so be it.
Hello there, this is not an entirely new technique, but then again, maybe the use case will be useful to someone, so I’ll go ahead and put it here. Indeed the idea came once again from the whatsapp group of Power BI User Group Barcelona, great people all around. Paul shared with the rest of us a solution he brought to a question in the Power BI Community Forum. I that thread (and answering the particular use case) a few solutions involved doing it all in Power Query, while Paul brought in a DAX approach to the solution. Beyond the particular use case of the question, this raises the question: how can we show individual measures in a tabular format? Continue Reading..
Once again I’ll recreate here a use case I found at work because I think it’s cool and with some gotcha’s that can be fixed.
The use case itself is broad enough. We are measuring the duration of an event (working hours, machine runs, etc) and we want to visualize it. Since we want to compare things, we need to graph a number, however, as humans, we might like to see 4h 30min instead of 270 min. Searching around in google you will find several approaches for transforming a number of minutes or seconds into higher units of time. And that’s a great starting point. For example here’s a great post by Reza Rad. Here we’ll just imagine we have minutes and we want hours and minutes.
Ok, I have maybe gone a bit overboard with the title, but I think it has some advantages over other hacks out there to establish the column widths of matrix, so here I am to explain how I came up with it and how to use it.
The first hack I saw was from Ben Ferris (aka The Power BI Guy) which added a dummy measure with a number of 0 to make the width (having automatic width enabled) and then it would disable automatic widths and remove the measure. Nice. But of course, if new columns appear you’ll need to set the thing again. Something similar happens with the approach of Bas, who skips the dummy measure thing and instead just plays with the format string to show the evenly wide number. His approach is cooler because you skip the measure thing, but you have the same weak points.
However, Bas’s video got me thinking on the topic and the role of format strings…
This may seem trivial, but it did not pop up in my head at first, so might be useful to somebody else.
In sales reports, there are lots of numbers. And if it’s a large company these numbers may be very large. So depending on the visual, the full number may be a bit too much, and having just thousands or millions is more than enough.