How to get a measure of Average of column percentage using DAX in Power BI for column chart?

Question

I am struggling with a problem as follows:

I need a DAX measure to find the average percentage from a matrix table result. This is what I get from the matrix table in Power BI

Place        Late No-Late Total
India         2     200    202
Bangladesh    40    60     100
China         0     150    150
Total         42    410    452

Then I apply % of Row Total

    Place        Late No-Late Total
    India         1%    99%   100%
    Bangladesh    40%   60%   100%
    China         0     100%  100%
    Total         9%    91%   100%

Now I need the average percentage from the "No-Late" Column which will be "86%" to plot it in the column chart. How can I achieve this part?

Answer 1

Can you please try the following

_avg = 
VAR _0 =
    SUMX (
        'fct',
        VAR _total = fct[Late] + fct[No-Late]
        VAR _noLate = fct[No-Late]
        VAR _pct =
            DIVIDE ( _noLate, _total )
        RETURN
            _pct
    )
RETURN
    DIVIDE ( _0, DISTINCTCOUNT ( fct[Place] ) )

Edit

if you want to display the total in a chart like below

please make sure your data source is like following. Because for Total to be displayed in a chart, the axis must present in the data source itself.

| Place      | Late | No-Late | Index |
|------------|------|---------|-------|
| India      | 2    | 200     | 1     |
| Bangladesh | 40   | 60      | 2     |
| China      | 0    | 150     | 3     |
| Total      | null | null    | 4     |

and then you can write following measure

_avg2 = 
VAR _currentlyVisiblePlace =
    MAX ( fct[Place] )
VAR _aggregation =
    SWITCH (
        TRUE (),
        _currentlyVisiblePlace <> "Total",
            VAR _0 =
                SUMX (
                    'fct',
                    VAR _total = fct[Late] + fct[No-Late]
                    VAR _noLate = fct[No-Late]
                    VAR _pct =
                        DIVIDE ( _noLate, _total )
                    RETURN
                        _pct
                )
            VAR _1 =
                DIVIDE ( _0, DISTINCTCOUNT ( fct[Place] ) )
            RETURN
                _1,
        _currentlyVisiblePlace = "Total",
            VAR _0 =
                SUMX (
                    ALL ( fct ),
                    VAR _total = fct[Late] + fct[No-Late]
                    VAR _noLate = fct[No-Late]
                    VAR _pct =
                        DIVIDE ( _noLate, _total )
                    RETURN
                        _pct
                )
            VAR _1 =
                DIVIDE (
                    _0,
                    CALCULATE (
                        DISTINCTCOUNT ( fct[Place] ),
                        ALL ( fct ),
                        NOT fct[Place] IN { "Total" }
                    )
                )
            RETURN
                _1
    )
RETURN
    _aggregation

Answer 2

Let's assume your table looks like the one below where the last three columns are calculated (just the sum and then the percentage)

By adding the following measures

No Late (%) = If( HASONEVALUE(Table1[No-Late (%)]),Table1[Total No Late]/Table1[Total], AVERAGE(Table1[No-Late (%)]))

Total No Late = sum(Table1[No-Late])

you will be able to get the following result in Power BI with the matrix visual