I wonder if anyone could help. I'm doing a process that requires me to do an addition:
[Total Charge] = [Total Transmission Charge] + [Total Distribution Charge]
where [Total Transmission Charge] is a measure which could be classified according to 3 dimensions:
(a) Connection Charge
(b) Interconnection Charge
(c) Rebate
[Total Distributed Charge] has not been defined in the data warehouse. I intend to set this up as a constant with, say, $15,000. This is only added once after the total of [Total Transmission Charge] has been determined.
I tried defining [Total Distributed Charge] as a parameter, as a calculated field, and as a dimension. The most obvious way is to define it as a parameter. When I create the calculated field [Total Charge] what happens is that the parameter [Total Distribution Charge] is added to each dimension under [Total Transmission Charge]. It becomes:
[Total Charge] = ( [Total Transmission Charge] Connection Charge + [Total Distribution Charge] ) + ( [Total Transmission Charge] Interconnection Charge + [Total Distribution Charge] ) + ( [Total Transmission Charge] Rebate + [Total Distribution Charge] )
In other words [Total Charge] has the [Total Distribution Charge] added to [Total Transmission Charge] 3 times instead of just once as I intended.
I tried using sum([Total Transmission Charge]) in the calculation to force Tableau to look at the total, and avoid defining [Total Distribution Charge] into 3 dimensions. But none work.
If there somewhere that I didn't get right? Should I change how the calculated field [Total Charge] is defined? Thanks.
Hi Joel,
Not sure if I get you 100%, but I' guessing Level of Detail expressions will help here.
Check out the calculated field "1 Total Charge" here or attached workbook:
- the way to write LOD expressions is
{fixed [DIMENSION]: sum([MEASURE])}
- by fixing at nothing, it means we are fixing at NO dimension and looking at the level of entire database. Hence, in "Total Charge", we use {fixed : sum([Total Transmission Charge])}
Let me know if this is what you're looking for.
Cheers,
Seinchyi