Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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,
Solved! Go to 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] )
)
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.
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.
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.
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] )
)
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.
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |