cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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,

2 ACCEPTED SOLUTIONS
Super User

Hi,

Please check the below picture and the attached pbix file.

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

Hi,

Please check the below picture and the attached pbix file.

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.