Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have report to show few dimensions with values in Pivot Table
Note : No measures and the dimensions are represented as KPI values with data types as text, number, percentage etc
RawData:
Customer | Product | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI6 | KPI7 | KPI8 | KPI9 | KPI10 |
X1 | A1 | 0.01 | AA | 1% | 25 | abc | |||||
X1 | A2 | 0.02 | BB | 2% | 30 | aabg | |||||
X1 | A3 | 0.01 | CC | 5% | 45 | hjk | |||||
X2 | A1 | 0.03 | DD | 65 | ar | ||||||
X3 | A1 | 0.05 | BB | 56.5 | aabg | ||||||
X3 | A2 | 1.12 | CC | 62 | hjk | ||||||
X3 | A3 | 1.887 | BB | 78.5 | aabg | ||||||
X3 | A4 | 2.159 | CC | 84 | hjk | ||||||
X5 | A1 | 2.431 | DD | 89.5 | ar | ||||||
X5 | A3 | 2.975 | BB | 100.5 | aabg | ||||||
X5 | A4 | 3.247 | CC | 106 | hjk | ||||||
X5 | A5 | 3.519 | DD | 111.5 | hjk |
Converted the raw data to KPI Names & Values using cross table for displaying in pivot table as
Customer | Product | KPI Name | KPI Value |
X1 | A1 | KPI1 | 0.01 |
X1 | A1 | KPI2 | AA |
X1 | A1 | KPI3 | 1% |
X1 | A1 | KPI4 | 25 |
X1 | A1 | KPI5 | abc |
X1 | A2 | KPI1 | 0.02 |
X1 | A2 | KPI2 | BB |
X1 | A2 | KPI3 | 2% |
X1 | A2 | KPI4 | 30 |
X1 | A2 | KPI5 | aabg |
X1 | A3 | KPI1 | 0.01 |
X1 | A3 | KPI2 | CC |
X1 | A3 | KPI3 | 5% |
X1 | A3 | KPI4 | 45 |
X1 | A3 | KPI5 | hjk |
Expected output:
A1 | A2 | A3 | A4 | |||||||||||||||||
KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | ||||||
X1 | 0.01 | AA | 1% | 25 | abc | 0.02 | BB | 2% | 30 | aabg | 0.01 | CC | 5% | 45 | hjk | |||||
X2 | 1.01 | AA | 9% | 35 | abc | - | - | - | - | - | - | - | - | - | - | |||||
x3 | 0.05 | BB | 56.5 | aabg | 1.12 | CC | 62 | hjk | 1.887 | BB | 78.5 | aabg | 2.159 | CC | 84 | hjk |
The challenge is there is no measure used in expression as sum or count etc
This is an urgent requirement and looking for an early help. Thanks in advance.
A1 | A2 | A3 | A4 | |||||||||||||||||
KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | |
X1 | 0.01 | AA | 1% | 25 | abc | 0.02 | BB | 2% | 30 | aabg | 0.01 | CC | 5% | 45 | hjk | |||||
X2 | 1.01 | AA | 9% | 35 | abc | - | - | - | - | - | - | - | - | - | - | |||||
x3 | 0.05 | BB | 56.5 | aabg | 1.12 | CC | 62 | hjk | 1.887 | BB | 78.5 | aabg | 2.159 | CC | 84 | hjk |
in the expression in the pivot table try this Text([KPI Value])