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
Community Champion
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
)