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.
I have a table with 12 rows for months that show the total sales amount for that month
each month also have a drop down menu with three sections that show 3 diferent product groups with the relevant sales amount.
Below the table is of course the Total number for that year which shows the total sales for all product groups for a whole year
what I want to do is to add similar three product groups below the Total so I can see the annual total for each of the three A, B , C product group. Each of them will show the sales amount for the 12 months.
How do I add that drop down menu below the Total?
Solved! Go to Solution.
Hi @mamedoff_korea ,
Are you referring to the creation of a column below the Visual Total row to view the information, according to the design of Power BI can not be achieved for the time being, but you can consider the creation of a new table to carry out the operation of the Union, in the row above the Total row to add the information.
Here are the steps you can follow:
1. Create calculated table.
Table2 =
VAR _table1 =
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Group],
"Value",
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
&& 'Table'[Group] = EARLIER ( 'Table'[Group] )
),
[Value]
),
COUNTX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
&& 'Table'[Group] = EARLIER ( 'Table'[Group] )
&& 'Table'[Month] = [Month]
),
[Value]
)
),
"Month", "Year_Value"
)
VAR _table2 =
UNION ( 'Table', _table1 )
RETURN
_table2
2. Create calculated column.
Rank =
IF (
[Month] <> "Year_Value",
VALUE ( 'Table2'[Month] ),
COUNTX ( ALL ( 'Table2' ), [Value] )
)
3. Create calculated table.
Table3 =
SUMMARIZE ( 'Table2', 'Table2'[Month], 'Table2'[Rank] )
4. Connecting the relationship between two tables.
5. Select Table3[Month] – Column tools – Sort by column – [Rank].
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @mamedoff_korea ,
Are you referring to the creation of a column below the Visual Total row to view the information, according to the design of Power BI can not be achieved for the time being, but you can consider the creation of a new table to carry out the operation of the Union, in the row above the Total row to add the information.
Here are the steps you can follow:
1. Create calculated table.
Table2 =
VAR _table1 =
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Group],
"Value",
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
&& 'Table'[Group] = EARLIER ( 'Table'[Group] )
),
[Value]
),
COUNTX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
&& 'Table'[Group] = EARLIER ( 'Table'[Group] )
&& 'Table'[Month] = [Month]
),
[Value]
)
),
"Month", "Year_Value"
)
VAR _table2 =
UNION ( 'Table', _table1 )
RETURN
_table2
2. Create calculated column.
Rank =
IF (
[Month] <> "Year_Value",
VALUE ( 'Table2'[Month] ),
COUNTX ( ALL ( 'Table2' ), [Value] )
)
3. Create calculated table.
Table3 =
SUMMARIZE ( 'Table2', 'Table2'[Month], 'Table2'[Rank] )
4. Connecting the relationship between two tables.
5. Select Table3[Month] – Column tools – Sort by column – [Rank].
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 |
---|---|
102 | |
101 | |
78 | |
69 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |