Aggregate based on certain filters

[Omega]

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!

[Greg_Deckler]

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]

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]

---

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