Getting the average of the % of column total grouped by categories

[Anonymous]

Hi,

I'm trying to calculate the average between the % of column total,

For example, I have this Table1 as example, the table has more columns and other categories, but I will sumarize it,

Table1

Date	--Categorie1	-Value
20/01/2024	--1	-100
20/01/2024	--1	-200
20/01/2024	--2	-150
21/01/2024	--1	-100
21/01/2024	--2	-150
21/01/2024	--2	-140
22/01/2024	--1	-20
22/01/2024	--1	-30
22/01/2024	--2	-40

From this table, I will sum the colunm "Value" and get the % from the total of each category, grouped by "Date",

Date	--Categorie1	--% from total
20/01/2024	--1	--66,67%
20/01/2024	--2	--33,33%
21/01/2024	--1	--25,64%
21/01/2024	--2	--74,36%
22/01/2024	--1	--55,56%
22/01/2024	--2	--44,44%

Now, I need to calculate the average between each %, grouped by category, my expected result should be:

TableExpectedResult

Categorie1	--% average
1	--49,29%
2	--50,71%

And my strugle here, is that I'm calculating the total sum of the column "Value", and calculation the fraction of the subtotal sum of each category, but it will give me a different result:

TableActualResult

Categorie1	---% over total
1	---48,39%
2	---51,61%

I would like to get my expected result as a measure, so I can work with slicers for different columns and types of categories,

I appreciate any kind of help, 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

expected result measure: =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "@totalvalue", CALCULATE ( SUM ( Data[Value] ), ALL ( Category ) ),
        "@categoryvalue", CALCULATE ( SUM ( Data[Value] ) )
    ),
    DIVIDE ( [@categoryvalue], [@totalvalue] )
)

[v-nuoc-msft · Accepted solution]

Hi @Anonymous 

@Jihwan_Kim Good share!

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure. Calculate the percentage of each category grouped by date.

% from total = 
VAR TOTAL_VALUE_CATEGORIE = 
    CALCULATE(
        SUM('Table'[Value]), 
        FILTER(
            ALL('Table'), 
            'Table'[Date]= MAX('Table'[Date])
        )
    )
VAR TOTAL_VALUE_DATE = 
    CALCULATE(
        SUM('Table'[Value]), 
        FILTER(
            ALL('Table'), 
            'Table'[Date] = MAX('Table'[Date]) && 'Table'[Categorie1] = MAX('Table'[Categorie1])
        )
    )
RETURN DIVIDE(TOTAL_VALUE_DATE, TOTAL_VALUE_CATEGORIE)

Create a measure. Calculate the average percentage for each category.

% average = 
AVERAGEX(
    SUMMARIZE('Table','Table'[Date], 'Table'[Categorie1], "% from total", [% from total]), 
    [% from total]
)

Here is the result.

Regards,

Nono Chen

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

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

expected result measure: =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "@totalvalue", CALCULATE ( SUM ( Data[Value] ), ALL ( Category ) ),
        "@categoryvalue", CALCULATE ( SUM ( Data[Value] ) )
    ),
    DIVIDE ( [@categoryvalue], [@totalvalue] )
)