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 Ci...
  • tamerj1's avatar
    tamerj1
    2 years ago

    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
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

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
    2 years ago

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