Percentage of a Table Column within a Fiscal Year date range

[SteCra100]

Hello... I have been searching the community and been unable to find this answer.

I have a table called Table 1 and two Columns called "FiscalMonth" and "NumberAsWords". The Column, "NumberAsWords" displays:

"One"

"Two"

"Three"

"Four"

as the result set. I want to create a measure to calcaute, within the fiscal months (01/04/2020 - 31/03/2020), how many times "One" was recored in the "NumberAsWords" column.

From there, I want to create a Stacked Bar Chart column. The "X" axis would be the Fiscal Month and the "Y" axis would show the  percentage out of 100% s i.e.

in April 2020 (as an example), the column would show:

20% "One",

40%, "Two"

40% "Three".

Firstly, is it possible to create a measure to calcualte this per month?

Secondly, is it possible to show this on a Stacked Bar Chart as a percentage of the Month's total, not the grand total?

Many thanks,

[v-rzhou-msft · Accepted solution]

Hi @SteCra100 

If you want to show percentage without using measure, you have to use 100% Stacked bar Chart.

And due to this visual can only sort by count of NumberasWords and Year Month, so we can't show most percentage at right(Top), least at left(Bottom). It will show the precentage as legend.

If you just want to use Stacked bar Chart, I update my measure:

Measure = 
VAR _Month =
    SELECTEDVALUE ( 'Calendar'[MonthNAME] )
VAR _Total =
    CALCULATE (
        COUNT ( 'Table'[NumberAsWords] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[FiscalMonth].[Month] = _Month
                && 'Table'[NumberAsWords] <> "Four"
        )
    )
VAR _Count =
    CALCULATE (
        COUNT ( 'Table'[NumberAsWords] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[FiscalMonth].[Month] = _Month
                && 'Table'[NumberAsWords] = MAX ( 'Table'[NumberAsWords] )
                && 'Table'[NumberAsWords] <> "Four"
        )
    )
RETURN
    DIVIDE ( _Count, _Total )

And set Format as percentage in Measure tools.

You can download the pbix file from this link: Percentage of a Table Column within a Fiscal Year date range

Best Regards,

Rico Zhou

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

[v-rzhou-msft]

Hi @SteCra100 

I think you don't need to build a measure, you can get the result by Stacked Bar Chart, Slicer and Filters directly.

I build a sample table which has FiscalMonth and NumberAsWords column.

Step:

Add FiscalMonth(use Date Hierachy keep Year and Month) into Axis.

Add NumberAsWords into Legend.

Add NumberAsWords and show as count.

Then remove Four from Filters(NumberAsWords)

Build a Slicer by  FiscalMonth(use Date Hierachy keep Year and Month).

Or you can build a Measure. You need to build a Calendar Table first and use it as a slicer. 

Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,04,01),DATE(2021,03,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthNAME",FORMAT([Date],"MMMM"))

Measure = 
VAR _Month =
    SELECTEDVALUE ( 'Calendar'[MonthNAME] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[NumberAsWords] ),
        FILTER (
            'Table',
            'Table'[FiscalMonth].[Month] = _Month
                && 'Table'[NumberAsWords] <> "Four"
        )
    )

The Steps to build a Stack Bar chart are the same as above, but you don't need to remove "Four" in Filter.

Result:

Left one is building directly, right one is using Measure.

You can download the pbix file from this link:  Percentage of a Table Column within a Fiscal Year date range

Best Regards,

Rico Zhou

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

[SteCra100]

Hello @v-rzhou-msft 

You have been a great help, thank you. I have used your Stacked Bar Chart, Slicer and Filters...

The only problem I have is that my count is not showing by percentage, just by count. How do I get my results to show by percentage, rather than using a measure?

Also, (if there is a way), to get the Stacked Bar Chart to show the most reuslts on top. I.e in January, if one = 10%, two = 40% and Three = 50%, Can I show the Stacked Bar Chart to have the resuts that have the most percentage on top of the stack, and the lowest at the bottom, if that makes sense!

Many thanks,

[v-rzhou-msft · Accepted solution]

Hi @SteCra100 

If you want to show percentage without using measure, you have to use 100% Stacked bar Chart.

And due to this visual can only sort by count of NumberasWords and Year Month, so we can't show most percentage at right(Top), least at left(Bottom). It will show the precentage as legend.

If you just want to use Stacked bar Chart, I update my measure:

Measure = 
VAR _Month =
    SELECTEDVALUE ( 'Calendar'[MonthNAME] )
VAR _Total =
    CALCULATE (
        COUNT ( 'Table'[NumberAsWords] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[FiscalMonth].[Month] = _Month
                && 'Table'[NumberAsWords] <> "Four"
        )
    )
VAR _Count =
    CALCULATE (
        COUNT ( 'Table'[NumberAsWords] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[FiscalMonth].[Month] = _Month
                && 'Table'[NumberAsWords] = MAX ( 'Table'[NumberAsWords] )
                && 'Table'[NumberAsWords] <> "Four"
        )
    )
RETURN
    DIVIDE ( _Count, _Total )

And set Format as percentage in Measure tools.

You can download the pbix file from this link: Percentage of a Table Column within a Fiscal Year date range

Best Regards,

Rico Zhou

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

[SteCra100 · Accepted solution]

Hello @v-rzhou-msft 

I cna't believe there was actually a percentage stakced bar chart! So I jsut used that chart to format the data into %.

Thanks for your help. I may have more questions, but will raise in a Forum!

Thanks again!

[amitchandak]

@SteCra100 , You can Try like

divide([measure], calculate([measure], allexcept(Table, Table[Month Year])))

or

divide([measure], calculate([measure], filter(allselcted(Table), Table[Month Year] =max( Table[Month Year]))))

[SteCra100]

Hello @amitchandak....

Thank you for your quick repsonse! Apologies, I should have mentioned this in my oringnal post... As mentioned, the column, "NumberAsWords" displays:

"One"

"Two"

"Three"

"Four"

as the result-set, however I only want to know the results of how many times "One" "Two" and "Three" are recorded. "Four" is just going to be excused from my results. 

I have two measures called "_1" and "_2": 

1st Measure: "_1"

= CALCULATE(

COUNTA('Table1'[NumberAsWords]),

'Table1'[NumberAsWords] IN { "One", "Two", "Three",}

)

*This is to find all records that just have "One", "Two", and "Three" in NumberAsWords column.

2nd Measure: "_2"

= CALCULATE(

COUNTA('Table1'[NumberAsWords]),

'Table1'[NumberAsWords] IN { "One", "Two","Three", "Four" }

)

**This is to find all records in NumberAsWords column.

Would you example you kindly provided, still work with the measures I have, or is it best to restart the entire measures again?

Many thanks,