Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table like below
PartnerID | Year | Business | Sales | Planned Sales |
A | 2016 | Storage | 500 | |
A | 2017 | Storage | 0 | |
A | 2016 | Compute | 300 | |
A | 2017 | Compute | 100 |
I need to get the sum (Sales) of 2016 where 2017 Panned sales is greater than 0 in set analysis.
My result should be 300.
THanks
Or if this is needed by PartnerID and Business, then I would create a new field in the script like this
LOAD PartnerID,
Year,
Business,
Sales,
Planned Sales,
AutoNumber(PartnerID&'|'&Business) as Key
FROM....
and then this
Sum({<Year = {2016}, Key = {"=Sum({<Year = {2017}>}[Planned Sales]) > 0"}>} Sales)
Try this?
Sum({<Year = {2016}, [Planned Sales] = {"=Sum([Planned Sales]) > 0"}>} Sales)
I tried, it doesnt give the result.
May be this
Sum({<Year = {2016}, Business = {"=Sum({<Year = {2017}>}[Planned Sales]) > 0"}>} Sales)
Or if this is needed by PartnerID and Business, then I would create a new field in the script like this
LOAD PartnerID,
Year,
Business,
Sales,
Planned Sales,
AutoNumber(PartnerID&'|'&Business) as Key
FROM....
and then this
Sum({<Year = {2016}, Key = {"=Sum({<Year = {2017}>}[Planned Sales]) > 0"}>} Sales)
Hi Sunny,
Many thanks for the reply, first option seems working, this is just the sample, Let me try with couple of scenarios also, the second option looks appealing to me, Let me try that too. will get back soon.
thanks.