Totals and average in one visual

[UsernameDSN]

Hi All,

I have a matrix with values, I have added grand totals, no issues. However I also need to add averages (of sum values, not average values) and percent of total. I have tried multiple measures and all of them work, but they also add values in the months. That way I end up with 3 values in each month. I need to have only one value in the month - sum value. Is there a way to do that? Thank you.

	Jun	July	August	September	October	November	December	Total	Average	Percent of total
Store 1	12000	12000	12000	15000	15000	15000	15000	96000	24000	60
Store 2	5000	10000	10000	10000	10000	10000	10000	65000	9286	40
Total								161000

[Anonymous · Accepted solution]

Hi,@UsernameDSN 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the calculated table I've created for your needs:

new = 
VAR sum1=SUM('Table'[value])
VAR avg1 =
    SUMMARIZE (
        'Table',
        [Column1],
        "month", "_AVERAGE",
        "value", AVERAGE('Table'[value])
    )
VAR total1 =
    SUMMARIZE (
        'Table',
        [Column1],
        "month", "_Total",
        "value", SUM('Table'[value])
    )
VAR Percent1=
SUMMARIZE (
        'Table',
        [Column1],
        "month", "_Percent of total",
        "value",SUM('Table'[value])/sum1*100
        
    )
 
RETURN
    UNION ('Table', Percent1,avg1,total1 )

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[UsernameDSN]

Thank you so much for taking time to help me! This is what I was looking for! thank you!