Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sujitjena
Resolver I
Resolver I

How to calculate percentage of total for categories by month/Quarter

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

Measure =CALCULATE(SUM('NCC'[Payroll]),FILTER('NCC','NCC'[Cost category]<>"Absence"))
 Measure query.PNG
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

11.png

 

Then use drill down and drill up in visual.

result dynamic month categories1.gif

 

 

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

12.png

 

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.

result dynamic month categories2.gif

 

 

 

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.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

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.

11.png

 

Then use drill down and drill up in visual.

result dynamic month categories1.gif

 

 

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

12.png

 

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.

result dynamic month categories2.gif

 

 

 

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.

Sumanth_23
Memorable Member
Memorable Member

hi @sujitjena - did you try using the "percentage of total" for the column value - please refer to the below screen grabs for reference. 

 

Sumanth_23_1-1600196480968.png

 

Sumanth_23_2-1600196513564.png

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

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 

 

Sumanth_23_0-1600198478146.png

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.