Column Chart and Showing Percentage of Total of Each Item per Year

[bdehning]

Let me ask this again on a simpler Clustered Columm Column Chart.  I am struggling and missing something.  

How do I replace the counts for each years column with a Percentage for that year, so 2018 would be Red 16.1%, Light Blue 9.7%, Orange 16.1%,Grey 25.8%, Green 19.4%, Blue 9.7%, Black 3.2%?  Would be great if I could have no decimal.   

Day Short is Text Field.  

[bdehning]

How can I can adjust rounding as even with 1 decimal 2020 is over 100%

[Mikelytics]

Hi @bdehning 

You have to write a measure and use it in the Y-axis:

% Share =
VAR __Base =
     COUNT(Table1[ColumnName1])

VAR __Total=
   CALCULATE(  
      COUNT(Table1[ColumnName1]),
      ALL(Table2[Column2))
   )

RETURN

DIVIDE(__Base,__Total)

Please replace the vatlues Table and column with your objects. Table1 and Table2 can be the same. Column1 ist the column which you use for counting (Total Gross ...). Column2 is the column which you use for the days (Day Short).

When you defined the measure you can also change the formatting to percentage.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[bdehning]

I ended up with this

[Mikelytics]

Can you please show the measure you built?

[bdehning]

% Share =

VAR __Base =

     COUNT(LossRunToExcel[Total Gross Incurred])

VAR __Total=

   CALCULATE(  

      COUNT(LossRunToExcel[Total Gross Incurred]),

      ALL(LossRunToExcel[Day Short]))

RETURN

DIVIDE(__Base,__Total)

[Mikelytics]

weird. 

for me it works just fine (see below) -> normal count

now with the share measure

Are you 100% sure that you used exactly this column as a legend? LossRunToExcel[Day Short])

Can you please share sample data? 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[bdehning]

Here is Visualization

[bdehning · Accepted solution]

This got me close as I had to use the Year Field and Day Short, but check out totals?  I get more that 100%  

Why is that?

Share Count Test =

VAR _count =

    COUNT( 'LossRunToExcel'[Total Gross Incurred] )

VAR _totalperyear =

    CALCULATE (

        COUNT( 'LossRunToExcel'[Total Gross Incurred]),

        FILTER (

            ALLSELECTED ( 'LossRunToExcel'),

            [Policy Inception Year] = MAX ( 'LossRunToExcel'[Policy Inception Year])

        )

    )

RETURN

    DIVIDE ( _count, _totalperyear )

 

[Mikelytics]

Hi @bdehning 

WHere do you get more than 100? In the first bunch its 100

how big is the deviation? is it maybe rounding?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[bdehning]

Try 2022.  I get 101 for several.    I am going to check out one decimal.   

[Mikelytics]

@bdehning 

as you see in genereal it works with percentage in my exampe but what might bother you is that now the scalig ist also absed on percentage. But as soon as you have the percentage value it should be possible to add a linechart so that you can use the percentage only for the data label.

[bdehning]

I only see one chose in Data Labels.   Series for Day of Week?