Adding calculated Total Line to Chart

[ashwani_gupta]

Hi All,

I have a Column Graph for which I want to add a Total Line. I have following 3 tables.

Table 1: Asset

Fields: Asset ID, Location City

Table 2: Users

Fields: User ID, Location City

Table 3: Region

Fields: Location City, Region.

Here, Regions are like Asia, Europe, North America, South America etc. Multiple Location City could be under one region for ex: under Asia, it could be Tokyo, Manila, Delhi etc.

I am trying to portray difference between No. of Assets and No. of Users in a Region and multiple by a Potential Cost of an asset for ex: 50 USD.

So, if Asia Region has 150 Asset and 100 Users then Additonal Cost for Asia will be 150-100 = 50 mutiplied by 50 USD = 2500 USD.

But if a region has less no. of Assets than users, Additional cost be zero. i.e. if Europe has 200 Assets and 225 Users then Additional Cost will be zero.

I have created a Measure in Table 1 'Asset' like:

AdditionalCost = if(

        COUNTROWS('Asset')-COUNTROWS('Users')>0,

        ((COUNTROWS('Asset')-COUNTROWS('Users'))*50),

        ((COUNTROWS('Asset')-COUNTROWS('Users'))*0)

    )

 With this Measure, I have created a column Chart with X-Axis as 'Region' from Table 3 and Y-Axis is Measure "Additional Cost" from Table 1. Table 1 'Asset' and Table 2 'Users' are connected to Table 3 'Regions' via field Location City. 

I want to add one more line in my Column Chart as "Total Addition Cost" which will be sum of all Additional Cost for Regions whereever No. of Assets is mroe than No. of Users i.e. Positive Additional Cost.

How can I do this? Please guide me.

[tamerj1 · Accepted solution]

Hi @ashwani_gupta 
Please refer to the attached sample file for more details

AdditionalCost = 
VAR CurrentRegion = SELECTEDVALUE ( Locations[Region] )
VAR Count1 = COUNTROWS ( Assets ) - COUNTROWS ( Users )
VAR Count2 = IF ( Count1 > 0, Count1, 0.00001 )
VAR Count3 = 
    SUMX ( 
        ALLSELECTED ( Locations[Region] ),
        VAR Count4 = CALCULATE ( COUNTROWS ( Assets ) - COUNTROWS ( Users ) )
        RETURN
            IF ( Count4 > 0, Count4, 0 )
    )
VAR AdditionalCount = IF ( CurrentRegion = "Total", Count3, Count2 )
VAR AdditionalCost = AdditionalCount * 50
RETURN
    AdditionalCost

SortingMeasure = 
IF ( 
    SELECTEDVALUE ( Locations[Region] ) = "Total", 
    - 0.00001,
    [AdditionalCost] / 10000000000
)

[ashwani_gupta]

Worked Perfectly

Thanks @tamerj1 

[tamerj1]

Hi @ashwani_gupta 
Do you mean you want to add a total column (bar) in the chart? Can you provide a small sample of data?

[ashwani_gupta]

@tamerj1 

Table 1: Asset

Table 2: Users

Table 3: Region

Measure added in Table 1: Asset

Graph Preapred:

Query: How to add an additional Bar "Total Additional Cost" in exisitng Graph. The "Total Additional cost" here will be USD 200. (Europe 150 + Asia 50 + Americas 0). Please note that for Region Americas - calculation of Additional Cost is ZERO as Americas has More Users than Assets (Asset Count is 5. User Count is 7).