Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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,
@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
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
@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
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:
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
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |