Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
matemusic
Advocate III
Advocate III

Multiple reports results to one major KPI table

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:kpi_list

 

 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.

1 ACCEPTED 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".Multiple reports results to one major KPI table1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.Multiple reports results to one major KPI table2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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".Multiple reports results to one major KPI table1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.Multiple reports results to one major KPI table2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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.

 

Multiple reports results to one major KPI table.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.