Pivot shows right information, pivot sum is not right, selections destroys it...
Hi,
i want to analyze som economical data for some companies and my data is structured like:
Company
Parameter
Period
Data
Company A
Revenue
2018
3000
Company A
Revenue
2017
2850
Company A
Revenue
2016
2700
Company A
Profit
2018
300
Company A
Profit
2017
200
Company A
Profit
2016
100
Company B
Revenue
2017
8000
Company B
Revenue
2016
6000
Company B
Profit
2017
1000
Company B
Profit
2016
800
Company C
Revenue
2018
5000
Company C
Revenue
2017
6000
Company C
Revenue
2016
5500
Company C
Profit
2018
250
Company C
Profit
2017
120
Company C
Profit
2016
200
I have about 20-30 companies that i want to analyze, not just three. I want to see sum revenue, for the selection of comapnies, for example 2018 compared to 2017. In this case Company B doesnt have any value registred for 2018 and in such case i would like to reuse the value for 2017.
A Pivot would look like this:
Revenue
Revenue
Revenue
Last
2018
2017
Company A
3000
3000
2850
Company B
8000
0
6000
Comapny C
5000
5000
6000
Sum of total
16000
8000
14850
I tried to make a measure "Last": If ( (Sum({$<Parameter={"Revenue"},Period={"2018"}>}Data) > 0 ) , (Sum({$<Parameter={"Revenue"},Period={"2018"}>}Data)) , (Sum({$<Parameter={"Revenue"},Period={"2017"}>}Data)) )
When i use the above on a Pivot table everything looks fine except the summary wich is wrong in column "Last" (it show the same value as in colum "2018" (8000).
If i make a KPI it only gets right when i select companies who all have values for "2018" or select all who doesnt have values for "2018".
I understand that my problem depends om my first If-line but i dont know how to solve it so every help would be valuable!
