Get avg of 100% stacked column chart values over time

[sewo]

Hi all,

I'm new to Power BI, hoping someone can help me out. 

I have a 100% stacked column chart with percentages over six monthsfor various slices (red, orange, yellow). The raw data has date and one color per row, and the goal is to see what percent of the total for a given date comes from each color.

How can I create a table where each row is the color and it's % values for each month? 

E.g.:

          Jan     Feb     March        Overall Average

red    10%     15%   5%                      10%

orange    7%

yellow

etc. 

I thought a measure might work, but I couldn't figure out the numerator. My working denominator was COUNT(colors).

Thanks!

[v-yuta-msft · Accepted solution]

Hi sewo,

To be general, maybe you can create a measure to achieve the average using DAX like pattern below:

Average Percentage =
CALCULATE (
    AVERAGE ( table[data] ),
    FILTER (
        ALLEXCEPT ( table, table[color] ),
        table[date] = SELECTEDVALUE ( table[date] )
    )
)

Regards,

Jimmy Tao