Forum Discussion

ashwani_gupta's avatar
ashwani_gupta
Frequent Visitor
2 years ago
Solved

Adding calculated Total Line to Chart

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.

  • 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
    )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    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's avatar
      ashwani_gupta
      Frequent Visitor

      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).

       

  • tamerj1's avatar
    tamerj1
    Community Champion

    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
    )