Report metrics on a weekly/daily basis

[jlie]

Hey all,

I'm currently trying to migrate our excel reports to PowerBI. We basically have a lot of metrics (that is: numbers of aggregated stats like sessions, goal conversions, average time on page, total revenue, etc) that we report on a weekly basis.

This was actually pretty easy to implement in excel, I basically implemented a lot of SUMIFs and COUNTIFs and thats it. Now I want this functionality in PowerBI. We do not need most of the the visualizations for management, because we simply steer our company from the numbers on this Metrics and not the looks of them. So what i basically need is a table with the last 10 weeks of data with 10-15 KPIs as rows in this table.

How could I implement this? I found out that the matrix is almost what I need - but I cant seem to find a way to group the date-values by calendar weeks and have the different metrics as rows in the table; matrix only allows me to group dates by yearh & month and have the metrics as columns.

Any help appreciated! Thank you very much

[Alexander24]

Hi There , 

I have a suggestion for you , 

1) To implement your KPI's you need create DAX formalaues to replicate your SUMIF's and COUNTIF's 

                         - The CALCULATE function will be your friend here (https://www.powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif/) 

2) To get the calendar grouping as you wish, you need to create a data model and create a seperate date table (which is best practice to use time intelligent functions in DAX) and you can use the function Weeknum () to get the weeknum (you link this date table to your table with all the data and then use the weeknum column in your pivot table)

3) For the last requirement, I have two suggestions

a) You can take a look at PowerPivot its free if you have the correct version of excel (i think its enterprise) otherwise in excel 2016 its called the data model - basically you can do all the same powerful dax calculations in Power BI and query multiple data sources using Power Query 

b) I currently use Power BI to create my data model and dashboards and then publish this to the Power BI Web Service, from there you can click on the 3 dots next to the dataset you have published and click analyse in excel , this downloads an odc file which you can use to analyse your Power BI Data model in Excel, you can then just refresh when you want new data

This way you can then drag metrics in the columns in a normal pivot table, 

Thanks,

Alexander

[jlie]

Thank you very much for this suggestions.

Unfortunately, the matrix does not work with either measures nor regular columns. I still only have my measures as columns, but not as rows. I don't get it. It seems like I'm the only person on the planet who has weekly/monthly KPIs that need to be compared and reported on a frequent basis.

Our reporting is currently in excel (2016, so we're using Power Query) and most of our KPIs are in a tabular format. It is absolutly critical for our management to compare the numbers, e.g. "last week we had 200, this week we have 210", so the pure visualizations wont do the job in my opinion). Is PowerBI maybe the completely wrong tool for "regular management by measurement"? If so, is there any alternative?

[Alexander24 · Accepted solution]

Hi ,

Good Point I have just realised yes you will struggle putting DAX measures in columns,

I know how to do this though which is good,

Basically you create a table (via excel and import or via 'Enter Data' in PBI ) with all the metrics you want to measure

i.e Metric Table

Metric Name                                  Rank

Average Bounce rate                      1

Average time spent on page          2

etc etc                                             3

You then put that into the data model (not connected to anything) you then write a measure like so

In quotations marks is reference to the name of the metric , and in [ ] is the DAX Measure

Metrics =  IF(HASONEVALUE(Metrictable[Metric Name]),SWITCH(VALUES(MetricTable[Metric Name]), "Average Bounce rate",[Average Bounce rate formaule],"Average time spent on page",[Average time spent on page formaule]),BLANK())

You then drag the column "Metric Name" into the column and then drag the measure [Metrics] into the values and then drag your week numbers into the columns

Basically, the formaule works abit like a VLOOKUP, it looks up the text value you specficy (So your spelling has to be perfect) and then swaps it with the formaule you tell it too,

The rank is there so you can sort by column (i.e sort metric name by rank) so you can then have the metrics in the order that you want,

Hope this helps and feel free to PM me if you need some more help offline,

Thanks

Alexander

[jlie]

This did the trick. Alexander, you are awesome. Thank you very much!

[Alexander24]

No Problem, another trick you may want to look at and implement is the use of Traffic lights in your matrix/pivots,

If you make the data model in PowerPivot you can add KPI's there (with thresholds against last week for example) and then when you import into your powerpivot workbook into PBI you can also use them there in a card for example (another good alternative to matrix)