Set Analysis in Pivot table

[Anonymous]

Hi All,

Can we use set analysis to read the value of the specific row ??

I mean if my rows dimension in table is KPI, can i get the value of the KPI2 using set analysis??

Please help

[sunny_talwar]

I am not sure I understand, can you share an example?

[Anil_Babu_Samineni]

Can you describe more with data and condition

KPI has vwhich values(I am assume, A B C D). And then which row value you want to took

Sum({<KPI = {'A'}>}Sales) // This will give only KPI of A. That's called Row wise i am assumption

[Anonymous]

I have a pivot table with shows KPIs(A, B, C) at various month(Jan, Feb, Mar) level. (KPIs are rows and Months are Columns).

I need to insert an extra row which should display the value of KPI1/KPI2 for each month.

Can I achieve this using set analysis?

Sum({<KPI = {'A'}>}Sales) filters the rows and displays only record where KPI='A'.

I need the records for KPIs A, B, C to be as it is and insert an extra record "ratio" at the bottom.

Please find the excel attached.

[sunny_talwar]

Check the attached

[Anonymous]

Thanks for your answer Sunny. But i also want the YTD in the columns. Can't we put pick in both Rows in Columns??

[sunny_talwar]

Check this now

[Anonymous]

Thank you Sunny. It worked very well.

But in the same way, can we get the ratio of BU to that of total Jan in the KP1.

something like  sum({<KPI={"Kp1"}>}Value)/sum({<KPI={"Kp1"},BU=>}Value) so that we can get ratio like 20/(20+20) =0.5.

[sunny_talwar]

I am not sure what you are looking to get.... can you share the expected output in an excel?

[Anonymous]

Please find the excel.