Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a lot of data, Costs and revenues. Together they make margin. What I want is to make a barchart with in the X-axes a categorised margin and in the Y-axis the number of contracts. it is like this:
My epression for the margin is like this:
(SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])
- SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Costs]))
/SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])
The expression for the number of contracts is just:
COUNT({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}DISTINCT [ContractNo])
Can anyone bring me in the right direction??
Thanks,
Halmar
you need to create a calculated dimension
the will go something like this
num(subfield(class(aggr(
(SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])
- SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Costs]))
/SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue]),[ContractNo]),0.1),'<',1),'#,##0%')
and use as expression your expression for counting contracts from above