Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Below is an example of a table of data which I've been asked to put into a visual:
Month_Year | AggPct1 | AggPct2 | AggPct3 | AggPct4 | AggPct5 | AggPct6 |
Jan-21 | 24.06 | 29.8 | 32.86 | 34.81 | 36.11 | 37.21 |
Feb-21 | 28.76 | 35.74 | 39.04 | 41.7 | 43.19 | 44.37 |
Mar-21 | 28.2 | 34.06 | 37.24 | 39.59 | 41.39 | 42.68 |
Apr-21 | 24.33 | 29.48 | 32.53 | 35.02 | 36.24 | 37.17 |
May-21 | 25.3 | 31.03 | 34.69 | 36.79 | 38.07 | 39.15 |
Jun-21 | 26.92 | 34.04 | 37.22 | 39.56 | 41.17 | 42.4 |
Jul-21 | 26.73 | 31.38 | 34.78 | 37.36 | 38.94 | 40.04 |
Aug-21 | 28.03 | 32.74 | 36.5 | 39.01 | 40.25 | 41.25 |
Sep-21 | 30.13 | 36.2 | 40 | 42.42 | 43.75 | 43.75 |
Oct-21 | 25.06 | 31.31 | 34.48 | 36.36 | 36.36 | 36.36 |
Nov-21 | 24.03 | 29.59 | 32.1 | 32.1 | 32.1 | 32.1 |
Dec-21 | 28.77 | 33.37 | 33.37 | 33.37 | 33.37 | 33.37 |
In both Excel and Power BI I can generate a line chart that looks like this:
Within Excel, right-click and Source Data, there's an option to Switch Rows and Columns:
That yields a chart like the image below. Personally I don't understand the benefit of this visual, but I'm only expected to replicate it at this time as we attempt to automate/move reporting to Power BI.
No matter what I try to do, I'm unable to replicate this "inversion" and am not enough of a data scientist to understand how to accomplish a change like this. What options - whether Power Query, DAX, measures, or something to do with the visuals themselve - do I have to accomplish this goal?
Solved! Go to Solution.
I think if you went into power query.
Selected your Month_Year column and then on the transform tab clicked unpivot other columns. You should then find it easier to build the graph you want.
Thank you - I had played around with pivot/unpivot but never got it quite right. This turned out like what was desired, and I can tweak from here... had to figure out axis, legend, values on the line chart after unpivot. Your help was much appreciated as this might save me from having to do manual work in Excel every month!
I think if you went into power query.
Selected your Month_Year column and then on the transform tab clicked unpivot other columns. You should then find it easier to build the graph you want.
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |