Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to create a measure with specific filter that can be shown as a percentage of total by cost categories. However, if i select months trend, it calculates total of 12 months and throws the percentage on that. How to create a measure to calculate a percentage of total on the Cost categories and then show by month. Should i change the graph or is there a way to tweak the measure.The measure I used is given below
Solved! Go to Solution.
Hi @sujitjena ,
You may try to put month and category in the axis of the bar graph, and then like A said, select Show values as Percent of grand total.
Then use drill down and drill up in visual.
Or you can try another idea of how it can be done using a bridging table.
1.Create Category and Month lookup tables, create a CategoryMonth table which is the cross product of Category & Month tables.
2.Duplicate each row of RegionYear and add an Axis Dimension column which is "Category" for half the rows and "Month" for the other half, and an Axis Value column which is the Category or Month value for each row (depending on the Axis Dimension value).
3.Relate Month and Category to CategoryMonth using inactive bidirectional relationships
4.Create measures.
Axis Dimension Selected =
IF (
ISFILTERED ( CategoryMonth[Axis Dimension] ),
IF (
CALCULATE ( HASONEVALUE ( CategoryMonth[Axis Dimension] ), ALLSELECTED () ),
VALUES ( CategoryMonth[Axis Dimension] )
)
)
Sales Amount = SUM ( 'Table'[Sales])
Sales Amount Flexible Axis =
IF (
NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
SWITCH (
[Axis Dimension Selected],
"Category",
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( CategoryMonth[Category], Category[Category] )
),
"Month",
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( CategoryMonth[Month], 'Month'[Month] )
)
)
)
The result is this.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sujitjena ,
You may try to put month and category in the axis of the bar graph, and then like A said, select Show values as Percent of grand total.
Then use drill down and drill up in visual.
Or you can try another idea of how it can be done using a bridging table.
1.Create Category and Month lookup tables, create a CategoryMonth table which is the cross product of Category & Month tables.
2.Duplicate each row of RegionYear and add an Axis Dimension column which is "Category" for half the rows and "Month" for the other half, and an Axis Value column which is the Category or Month value for each row (depending on the Axis Dimension value).
3.Relate Month and Category to CategoryMonth using inactive bidirectional relationships
4.Create measures.
Axis Dimension Selected =
IF (
ISFILTERED ( CategoryMonth[Axis Dimension] ),
IF (
CALCULATE ( HASONEVALUE ( CategoryMonth[Axis Dimension] ), ALLSELECTED () ),
VALUES ( CategoryMonth[Axis Dimension] )
)
)
Sales Amount = SUM ( 'Table'[Sales])
Sales Amount Flexible Axis =
IF (
NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
SWITCH (
[Axis Dimension Selected],
"Category",
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( CategoryMonth[Category], Category[Category] )
),
"Month",
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( CategoryMonth[Month], 'Month'[Month] )
)
)
)
The result is this.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @sujitjena - did you try using the "percentage of total" for the column value - please refer to the below screen grabs for reference.
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
Proud to be a Super User!
@Sumanth_23 : This is exactly what used for the measure. The problem is it calculates percentage on the total of 12 months and not on the categories. What i want is it should calculate percentage on the total of categories for each of the months.
Hi @sujitjena - Maybe you should try using a stacked chart as seen in the below screen grab
I am not able to understand the exact scenario - would you be able to share a sample dataset for reference
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |