Compare Any Two of Anything in Power BI - Excelerator BI

Compare Any Two of Anything in Power BI

I have been increasingly recording videos as my method of sharing tips and techniques for getting more out of Power BI. Some concepts are best documented (traditional blogging) and some are just easier for everyone if it is a video. That is the case again for today.

Compare Any Two of Anything in Power BI

The whole concept here is to allow a user to pick two things from a list and compare them with each other. For example, compare

  • Any year with any other year
  • Any product against any other product (maybe even a group of products, using multi select)
  • Snapshots (eg different versions of a financial forecast) with any other version
  • Etc

What is possible is endless, and the good news is that it is pretty simple to do this in Power BI. Check out my video below to see how I did it.

Final Result

You can see in the image below, the user can select a year from the top slicer and any other year from the bottom slicer, and the visual updates along with the title of the visual clearly communicating what is being displayed.

Compare Any 2 Of Anything

If you would like to learn more about Power BI from experts like me, Ken Puls, Miguel Escobar, and Reid Havens (all Microsoft MVPs), head over to http://skillwave.training and check out our training course catalogue. Yes, you can learn yourself using YouTube – there is a lot of great free content out there (like my video below), but you will learn better/faster if you take a structured course built by industry professionals.

Video

21 thoughts on “Compare Any Two of Anything in Power BI”

  1. Dear Matt,
    great explanation, this helps me an important step further.

    However, I do question:
    in your example, each collumn in the matrix is corresponding with one filter (year).
    However, how to deal with each collumn in the matrix has to respond to two filters (let say: year and category)?
    Thanks in advance for your insights!

  2. Hi Matt. Great video. Simple and works! A follow up question if possible…
    If we select multiple months to compare, say 3 months, the matrix correctly displays the 3 individual month sales, but the comparison sales is the sum of all 3 months selected in the comparison date slicer, not comparing each month selected to it’s counterpart.
    To simplify the problem, rather than dynamically identifying which of the columns selected to be compared against, if we hard coded the comparison feature, say, wanting to compare sales from July from the current year with July from the previous year, would this DAX be easier to code for the Comparison Sales column?
    Thanks in advance and happy to chat offline.

    1. There are many different ways to solve such problems. This approach is how to do it dynamically comparing 1 item with another. I see no reason why you couldn’t use this technique using months and have those months combine to compare “these 3” with “those 3”. Or you can hard code it – it’s just it is not dynamic then.

  3. Henk-Jan van Well

    Great demo! And could help me to get nearer to my search for the explicit definition of the implicit measure Difference to.. which come for free with each pivot table in Excel. I read every now and then that implicit measures are under the hood “just” DAX-formulas…but I have never seen so far a 1:1 translation. This technique seems to do the trick…for every filter on your data define an inactive comparison filter… and I guess you can even calculate differences for multiple filters, i.e. (year, product) vs (comparison year, comparison product) etc. Would you by chance happen to know any documentation, link on the DAX code used for implicit measures ?

    1. I’ve looked and never found any. I have actually spoken to MS about this, Will Thompson and Kasper de Jonge, from memory. There is a technical difference to the way Power Pivot handles this, and the way it is done in PBI. In Power Pivot, there is actually a measure generated and stored in the Power Pivot model (implicit measure). If you do this in PP, then migrate the model to PBI, you can actually see the implicit measure, DAX and all. PBI handles it differently, more like a regular pivot table (without Power Pivot). The software has a set of standard functions (sum, count, min, max, etc) that it allows the user to apply to a column when used in a visual (in some locations). There is no actual measure generated, but the use of the Columns in this way causes the tool (PBI) to generate the aggregations automatically under the hood. The difference is transparent to the user, but technically there is no implicit measure in PBI, but there is in Power Pivot. So the official line I was told by MS is there are no implicit measures in PBI, just implicit calculations. Now, having said that, I believe it is useful to refer to implicit measures, as everyone seems to use that term.

      When you write a measure, you are writing some DAX code which is sent to a pre-compiling engine before being sent to the formula engine and storage engine for processing. When you add a column into a values well in PBI, the UI understands what you want (sum is default, but it can be changed). Instead of writing A measure, the UI sends the request straight to the compiler and the same process is executed. If you want to sum a column, or write DAX for SUM(table[column]), what is sent by the complainer is identical.

      Hope that helps.

      1. Henk-Jan van Well

        Thnx for the swift and comprehensive answer and nice to read you have looked for this yourself 🙂
        I will have a look into your suggestion to migrate the PP model to PBI as that might shed some more light on this illustrious issue. Best, HJ

  4. Wonderful :). Then the next question is… how to filter comparison slicer so it doesn’t show selected items from non-comparison slicer?

  5. Very concise and clear explanation.
    Sometimes we come across people who clearly know a lot about a certain subject but cannot explain it in a simple way, which is not the case with Matt Allington.
    Thank you.
    JM

  6. You made it look so easy! Thanks for that

    On a side note, your Power BI runs really smoothly. I find mine can be really choppy and lags a lot. I experience this on both my work and personal machine, each with 8GB ram. Is that insufficient for Power BI ?

    1. Sometimes I edit out the spinning wheel so you don’t have to wait for Power BI to update. 8GB is the minimum and should be fine unless you have 10’s of millions of rows of data.

  7. Hey Matt, some of us poor souls have an IT department that restricts access to YouTube etc. …any chance you could provide both the video and a more traditional blog for your solutions?

    1. Hey Mat. I sympathise for you. Companies that don’t allow access to YouTube are dinosaurs. I say companies and not IT, because the IT department is only as good as the leadership of the company. Read my article about that here https://p3adaptive.com/2016/10/company-suffer-battered-syndrome/

      While I would like to help, it takes 2-4 hours to produce either a video or a blog, and I don’t have the extra time to do both. Time for you to start lobbying your company for a more progressive approach to IT 🙁

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top