Forum Discussion
Please Provide Direction on Creating KPI Dashboard
I'm working on startup and would like to take our current Excel manual entry KPI dashboard and replicate it in Power BI. My first thought was a pivot table but as I'm putting the date columns in, measures just aren't aligning with the date groups, and values keep showing up in the header, making it unreadable.
Essentially I'm just looking for some form of direction on how I can create this in Power BI.
5 Replies
sevenhillsSuper User
I have some idea to do this.
Normally if it is of same type i.e., only dollars or only percentages, then we can use the option "Values" as "Show on rows". I see that you have multiple formats.
It is possible and little complex for new folks. With little effort, you can do this.
What is your data model, purely with respect to measures and how deep are those calculations going to be in the matrix visual?
I am fairly new with Power BI so your help is massively appreciated. There will need to be a "calculation" or "measure" for each KPI. For instance, using the data table screenshotted below, I could write something like this for trailing 30 days % of total machines w/ trx
Trailing 30 days % of total machines w/ trx = divide((calculate(DISTINCTCOUNTNOBLANK(Sale[AtmId]),'Sale'[Fiat]>0)),calculate(DISTINCTCOUNTNOBLANK(Sale[AtmId])))
Again, thank you so much for your help.- sevenhills
Since you are new to Power BI, I would like to inform that it is definitely possible and may advise you to take an iterative approach.
I will recommend to start with one calculation group, with 5 Measures
which contains different formats and then add the remaining.1. Cleanse the data and model to your needs
2. Create the measures you need
3. Get the Tabular Editor installed (Optional but recommend to ease your work)
4. Calculation GroupsI will recommend to go one after other these links, which makes you more comfortable.
Spend like 3 to 4 hours, as almost all links provide different examples using Calculation Groups.- https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/
- https://www.sqlbi.com/tv/introducing-calculation-groups-in-dax-and-power-bi/
- https://apexinsights.net/blog/10-uses-for-calculation-groups
- Also, check towards the end, Full list of references
- https://community.powerbi.com/t5/Community-Blog/How-And-When-To-Use-Calculation-Groups-In-Power-BI/ba-p/1796197
- https://blog.enterprisedna.co/small-multiples-with-calculation-groups-in-power-bi/
- https://www.youtube.com/watch?v=XCugcQ-_Zfk
- https://blog.enterprisedna.co/creating-measure-groups-power-bi-best-practices/
- https://blog.enterprisedna.co/conditional-formatting-in-calculation-groups-power-bi/
- https://goodly.co.in/measures-in-columns-matrix-powerbi/
- https://blogs.perficient.com/2022/08/29/calculation-groups-in-power-bi/
- https://learn.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallproducts-allversions
- https://exceleratorbi.com.au/dynamic-formatting-of-switch-measures/
5. You may need two or three Calculation Groups, as you have lagging, leading indicators set
I would literally pay to have help on solving this. It's critical for our company.
