Skip to main content

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.

14 answers
  1. Jul 17, 2015, 6:17 AM

    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

0/9000