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 us...
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

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