In the data source pane you can simply select all these 4 fields except name, right click and click pivot. With this you will get 4 rows for each row in your source like:
David, KPI 1 Num,40
David, KPI 1 Denom,80
David, KPI 2 Num,50
David, KPI 2 Denom,60
It is not exactly what you are looking for but i think this should also work for you.
Other option is to 2 union the same dataset.
Thanks for your reply.
It's important that the data has the KPI Name - Numerator - denominator structure to it so it will function well with other work i am doing.
Having each numerator and denominator be its own line would also create work for creating percentages
could you explain your union thinking?
Ok. In union you can do union the same dataset with itself.
So let say your first instance of filed is called file1 and second file2. A new field table name will be created with there 2 names.
then create a calculated field that KPI that says:
If table_name='file1' then 1 else 2 end'
numerator will be :
If table_name='file1' then [KPI 1 numerator] else [KPI 2 numerator] end
denom will be
If table_name='file1' then [KPI 1 denom] else [KPI 2 denom] end
Now use these 3 fields + name for your calculation.