Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Sorry, this seems simple but cant wrap my head around it.
I have a dimension 'SubType'
Within this dimension, I have a number of items measuring electricity for example [Electricty], [Renewable], [RECs]
I'm trying to create a Piechart that shows the portion of Renewable to Electricity. For this, i need to subtract the [RECs] from the [Electricity]
This attempt of course shows me the proportions of all the data.
SUM({$<InvoiceSubTypeName={[Electricity],[Renewable],[RECs]},PeriodFinancialYear={'$(vReportingFiscalYear)'}
>} TotalUsage)
Do i need to do this before i aggregate it in the set expression? A custom Field perhaps?
any advice most welcome
Are you saying that you are looking to sum(TotalUsage) where SubType is not RECs? May be this
Sum({$<InvoiceSubTypeName -= {'RECs'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage)
You are right in that I do not want to see the [RECs] in the piechart and understand -= will exclude them from the visualisation.
I want to subtract [RECs] from [Electricity] and have the Piechart show the results.
This is what I don't understand.... what does subtracting RECs from Electricity even mean? These are some values in a single row? are they within multiple rows? May be you want this?
Sum({$<InvoiceSubTypeName = {'Electricity'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage)
-
Sum({$<InvoiceSubTypeName = {'RECs'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage)
But again, I am not sure what you really mean when you say 'subtracting RECs from Electricity' and may be you won't get what you want using the above expression
Hi thanks for bearing with me,
SubType | RECs | Electricity | Renewable |
TotalUsage | 100 | 400 | 100 |
Consider the above values
I'd need to subtract [RECS] from [Electricity] and then build a Piechart that will show Electricity= 300 and Renewable =100
May be this
RangeSum(
Sum({$<PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage),
If(InvoiceSubTypeName <> 'Renewable', Sum(TOTAL {$<InvoiceSubTypeName = {'RECs'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage))
)