Forum Discussion

gustavoluis's avatar
gustavoluis
Regular Visitor
4 years ago
Solved

insert grand total in chart

Dears, i need help please.   I have the graph, which is the result of counting open and answered calls per week. but at the end I would like to add the grand total with the sum of the selec...
Need Help
Tips and Tricks
Tutorial Requests
danextian's avatar
danextian
Icon for Super User rankSuper User
4 years ago

Hi gustavoluis ,

 

For this, you would have to make use a disconnected (no relationship with Facts/Dimension tables) table that contains all the unique items in a column (week number, etc) plus another row for Total and use that column in a visual.

 

Sample calculated table to create a disconnected table. 

Categories with total = 
VAR __CAT =
    ADDCOLUMNS ( DISTINCT ( Data[Category] ), "Sort", BLANK () )
VAR __TOTAL =
    ROW ( "Category", "Total", "Sort", 10 )
    //Sort column to be used to sort the Category column by where Total is the last one in ascending order. The rest are sorted alphabetically.
RETURN
    UNION ( __CAT, __TOTAL )

 

Sample measure to include the total.

Sum of Value with total = 
VAR __CAT =
    SELECTEDVALUE ( 'Categories with total'[Category] )
VAR __TOTAL =
    CALCULATE ( [Sum of Value], ALLSELECTED ( Data[Category] ) )
RETURN
    IF (
        __CAT = "Total",
        __TOTAL,
        CALCULATE (
            [Sum of Value],
            Data[Category] = SELECTEDVALUE ( 'Categories with total'[Category] )
        )
    )

 

sample result

 

sample pbix

https://drive.google.com/file/d/1oUR5D1YftbCs8gDLDnxYq_QjYJmKhqhM/view?usp=sharing