How to create a new bar in Clustered column chart showing total of three Months

[harirao]

Hello, I have a clustered bar chart, were i have taken Attributes as Months and Values, selected only three months, now i want to add a bra which should be sum of three months values

How can I do this in Power Bi?

I am looking similar solution as created in MS Excel.

Please assist me.

Thank you!

[harirao]

@joaoribeiro 

As per the solution mentioned above from @Arul, i have Create a calculated table for months.
so now i am unable to see 'STARTOFMONTH(date)' in Data


Thank you

Regards,

[joaoribeiro]

@harirao  you can add one more column to your auxiliary table. Please consider the code below, and adjust as needed for your specific case:

Previous Q =
UNION(
DISTINCT(SELECTCOLUMNS(Unpivot_Data, Unpivot_Data[Attribute], Unpivot_Data[StartOfMonth])),
ROW("Column1", "Previous Q", "Column2", 99999)
)
//Column2 as 99999 will set the Previous Q line with a date in the future, so it will be sorted as the last value always

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[harirao]

Hi @joaoribeiro 
Is there any other solution for Sort Months?
Still not getting correctly result, after doing above mentioned work around.

Data is not visible after adding Column2


Thank you

Regards,

Hari

[harirao]

@Arul 

By using the above logic i got the solution, now facing the challenge to Sort the Month according to calender like (Feb, Mar, April), which is not working correctly.

Please assist me on this


Thank you

[joaoribeiro]

Hi @harirao ,

In order to have proper sorting for non numeric values, you can use other column as base. In this case that you are using a month-year column, you can follow the steps below:

1. Create another column in your Date table for STARTOFMONTH(date)
2. Select the month-year column  > Sort by Column > Start of Month
3. After that, the sorting on the visual will follow the same sorting of the date.

You need to create the start of month column, because the "Sort by Column" option will only work if you have unique matches between the two columns, so the "date" column would not work, because there are multiple dates for the same month.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[Arul · Accepted solution]

@harirao ,

try the following steps.

1. Create a calculated table for months and then total to be shown in the visual,

Months = 
UNION(
    VALUES(
        Table[Month Name]),
        ROW("Column1","Total")
)

2. Create a incative relationship between newly created table and the existing table with Month name as a common key between the tables,

3. Write the below measure,

Sales Total Measure = 
VAR _selected = SELECTEDVALUE(New Table[Month Name])
VAR _result = IF(
    _selected = "Total",CALCULATE(
        SUM(Table[ Sales]),
        ALLSELECTED(New Table[Month Name]),
        USERELATIONSHIP(Table[Month Name],New Table[Month Name])),
    CALCULATE(
        SUM(
            Table[ Sales]),
            Table[Month Name] = _selected)
)
RETURN _result

4. Use the newly created month names and measure in the visual,

Thanks,

Arul

[joaoribeiro]

Hi @harirao, I believe this video can help you achieve your objective - I tested it myself and it worked:

https://www.youtube.com/watch?v=Tbvi7FaybQ8

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
João Ribeiro
Senior BI Consultant