Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
for our company we made about 15 reports for different topics (Revenue, Accuracy of the supplier, Accuracy to the buyer, Stock, Production success, Labor productivity and so on...). We used KPI visuals and pinned them to the dashboard - we have overall about 30 KPI visuals on dashboard.
But those visuals are consuming too much space and they don't show all information, we want to see.
So, now we want for all those reports to have just one KPI table with all key indicators for each of the topic - sometnhing similar like this:
Is there a way, to join key results from different reports to one report?
We already tried to build one model with all relevant tables and use just relevant measures, but the model is becoming too big and complicated.
We tried to connect reports to Excel and then publish Excel to Power bi online, but it still doesnt work. Is there a way to get that result?
Thank you all.
Solved! Go to Solution.
Hi @matemusic,
I'm afraid we can't create a report from several different reports / models. You can submit an idea here: power-bi-ideas. There may be two workarounds. One is yours. The other one is using an online Excel, which isn't perfect one.
1. Install "Microsoft Power BI Publisher for Excel".
2. Connect to the report, and PowerViot the target measures.
3. Create a summary table in a new sheet.
4. Upload this workbook into OneDrive.
5. In Power Service, "Get data" get the workbook from OneDrive as an Excel Online.
6. Everytime update the excel, then reload it in the Power BI Service, the KPI table would be updated.
Best Regards!
Dale
Hi @matemusic,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hey, sory..
Unfortunately, my problem is not to create a table with different measures, but to get measures from several different reports / models into one common KPI table.
The final result I'm looking for is a dashboard that contains one table with multiple KPI data instead of several KPI visualizations.
For now we need to build one huge model (instead of using existing, smaller models) that is too large and too complex for maintenance and normal operation.
Hi @matemusic,
I'm afraid we can't create a report from several different reports / models. You can submit an idea here: power-bi-ideas. There may be two workarounds. One is yours. The other one is using an online Excel, which isn't perfect one.
1. Install "Microsoft Power BI Publisher for Excel".
2. Connect to the report, and PowerViot the target measures.
3. Create a summary table in a new sheet.
4. Upload this workbook into OneDrive.
5. In Power Service, "Get data" get the workbook from OneDrive as an Excel Online.
6. Everytime update the excel, then reload it in the Power BI Service, the KPI table would be updated.
Best Regards!
Dale
Thank you @v-jiascu-msft, that is similar solution we are looking for.
Big problem here is refreshing. Maybe we wil try to use macro in Excel, which will refresh data every day.
Hi @matemusic,
Using macro in Excel is a wonderful idea. I have encountered refreshing problem of excel several times. If you work it out, please share the code with me (and the community). I will try it too. Thank you in advanced.
Best Regards!
Dale
If we will use this method i will share the code. Currently there are questions about stability of using this method.
Hi @matemusic,
I think the only way to find out the stability is trying it in a environment similar to the production.
Best Regards!
Dale
Hi @v-jiascu-msft,
i found this code on the reddit (https://www.reddit.com/r/excel/comments/3i6hf8/code_in_vba_to_automatically_save_a_file_every_15/😞
'ThisWorkbook:
Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:00:10"), "SaveWb" End Sub
Module1:
Sub SaveWb() ThisWorkbook.Save Application.OnTime Now + TimeValue("00:02:00"), "SaveWb" End Sub
Now we wil testing connected Excel to 15 Datasets over night. I will report how it will go.
Hi @matemusic,
Thank you very much for sharing. I am eager to know the result.
Best Regards!
Dale
Hi @v-jiascu-msft.
I tested it. Problem is, that somethimes connection fails, so user has to click OK on warning message for each failed connection. After that user can refresh data manually with no problem (or can wait for next auto refresh). When refresh is running over night, there is no user to click OK, so refreshing process is stopped. That is a huge problem, which I dont't know how to resolve it.
Hi @matemusic,
Thanks again for sharing. I have tried to do it with Microsoft Flow. But it didn't work.
Best Regards!
Dale
Hi @matemusic,
The visual will be very complicated whatever the visual is if we put all 15 KPIs into it. There is a workaround. Please have a try.
1. Create a new table named "KPImeta" like this:
Type ID
QuantityKPI 1
SalesKPI 2
PurchaseKPI 3
2. Create measures for each KPI if you didn't use a measure for indicator or target.
3. Create measures of target and indicator for the final KPI table .
13KPIindicator = IF ( HASONEVALUE ( 'KPImeta'[ID] ), SWITCH ( VALUES ( 'KPImeta'[ID] ), 1, [11QuantityKPI], 2, [12SalesKPI], 3, [10PuchaseKPI] ), 9999 )
17KPItarget = IF ( HASONEVALUE ( 'KPImeta'[ID] ), SWITCH ( VALUES ( 'KPImeta'[ID] ), 1, [14PuchaseLastyear], 2, [15QuantityKPIlastyear], 3, [16SalesKPI] ), 9999 )
4. Create a slicer with the column of 'KPImeta'[type]. Create a KPI with the two measures. Finally, you can choose the KPI from the slicer to show up in the KPI visual.
Best Regards!
Dale
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
36 | |
30 | |
18 | |
13 | |
8 |
User | Count |
---|---|
50 | |
36 | |
30 | |
15 | |
12 |