Percent of Total and Percent of Subtotal by Month/Year

[VC905]

Hello, 

I've done alot of research on creating dax measures and I can't figure out how to get percent of total and category total when there are years/months in the data.

1. I would like a measure for Share of Market to calculate percent of the total volume for that month/year that is shown.

2. I also want to show share of segment where the volume per month is divided by the total of the segment only (not the grand total)

Hoping this simple data chart illustrates this.

[v-yifanw-msft]

Thank @Wilson_  and @Ashish_Mathur  for their prompt replies.

Hi @VC905  ,

Depending on the information you have provided, I created a sample data to help you solve your problem. You can follow these steps below:

1.Add new column.

Share of Market = 
VAR _1 = 'Table'[Segment]
VAR _2 =
    CALCULATE ( SUM ( 'Table'[Volume] ), FILTER ( 'Table', 'Table'[Segment] = _1 ) )
RETURN
    DIVIDE ( 'Table'[Volume], _2 )

Share of Segment = 
VAR _1 = 'Table'[Brand]
VAR _2 =
    CALCULATE ( SUM ( 'Table'[Volume] ), FILTER ( 'Table', 'Table'[Brand] = _1 ) )
RETURN
    DIVIDE ( 'Table'[Volume], _2 )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the download link of that Excel file with your formulas already written outside the Pivot table.

[Wilson_]

Hi VC905,

Probably my own personal failing, but I'm not following your calculations. Can you walk through them in a more step by step way, for the first line for example?

[VC905]

Probably my fault for not explaining it. I can't attach a file so I'll try screenshots with the formula. I will aslo try the solution from v-yifanw-msft and see if that works.

Essentially - Share of market refers to the full market regardless of what segment it is in. I figured out how to do this one.

Share of Segment I only want it to divide by the total of that specific segment its in.  (For Example BMW as a total car market has a 5% share but in the luxury market it has 30%)

[Wilson_ · Accepted solution]

Hi VC905,

Try the below for your share of segment measure:

Share of Segment =
VAR TotalSegment =
CALCULATE (
    SUM ( Table[Volume] ),
    ALLEXCEPT ( Table, 'Calendar', Table[Segment] )
)
VAR Result =
DIVIDE (
    SUM ( Table[Volume] ),
    TotalSegment
)

RETURN Result

Of course, replace Table with the actual table name and if you have one, replace SUM ( Table[Volume] ) with your relevant measure.

I suggest using a measure like this instead of using calculated columns like @v-yifanw-msft is suggesting. Calculated columns are generally not best practice.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[VC905]

Thanks this worked. Sorry for the late response.

[Wilson_]

Perfect, glad that worked for you. Thanks for the update. 😄