Forum Discussion

matemusic's avatar
matemusic
Icon for Advocate III rankAdvocate III
9 years ago
Solved

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:

 

 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.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

12 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

    • matemusic's avatar
      matemusic
      Icon for Advocate III rankAdvocate III
      9 years ago

      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.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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