Forum Discussion

Omega's avatar
Omega
Impactful Individual
9 years ago

Aggregate based on certain filters

Hi,

 

 

I have the below tables and I am trying to calculate KPI% in the 2nd table which is the total QTY cancelled per reason/total QTY ordered:

 

 

Please assist :)

 

Thanks!

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    If I read this correctly, you should just need the first table and have to create a measure like:

     

    KPI% = SUM([Total cacelled QTY per reason]) / SUM([Quantity Ordered])

    Create a table visualization with the columns you have in your second table along with the measure.

    • Omega's avatar
      Omega
      Impactful Individual
      9 years ago

      Thanks but when I create the 2nd table, KPI% = NAN or 100% :(

       

      From Table 1, I have to calculate the summation of qty cancelled per reason and then divide by the total qty ordered per week. 

       

      Hope this clarifies. 

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago
        Omega wrote:

        Thanks but when I create the 2nd table, KPI% = NAN or 100% :(

         

        From Table 1, I have to calculate the summation of qty cancelled per reason and then divide by the total qty ordered per week. 

         

        Hope this clarifies. 

        Omega

        Greg_Deckler's measure shall work. For NaN or 100%, I guess you may forget to change the measure format.

         

         

        By the way, how you created the 2nd table? I think you shall use a Table visual? For any case, check the attached pbix file.