cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Total Sum of a measure

Hi experts, hope you all doing good.

Sorry guys if my English is weak.

I have a table like this where I calculated the earned percentage.
Now I want to calculate the Score of each row, which I have to multiply this earned percentage by 20 and have the sum total of these rows at the end of the table.

Actually, I need to have the number 130 at the bottom of the table instead of the number 15.
my measure I use is:
Earned percentage = CALCULATE(SUM('SLS3 InvoiceItem'[Carton])/CALCULATE(SUM(Target[Target])))

Score = [Earned percentage]*20

I also read this post but still could not solve my problem

1 ACCEPTED SOLUTION
Community Support

Hi, thanks for the information you have given.

When creating the "Score" field using "Measure", the "Total" of the Score is calculated using the Total of the "Earned percentage", which is 77%.
If you want the result to be 130 instead of 15, you need to create the field "Score" using "Add column".

Or use the following DAX to create the field "Score" with Measure:

Score_measure = SUMX('Target',[Earned percentage]*20)

Detailed screenshot：

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Joseph Ji

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

5 REPLIES 5
Community Support

Hi, thanks for the information you have given.

When creating the "Score" field using "Measure", the "Total" of the Score is calculated using the Total of the "Earned percentage", which is 77%.
If you want the result to be 130 instead of 15, you need to create the field "Score" using "Add column".

Or use the following DAX to create the field "Score" with Measure:

Score_measure = SUMX('Target',[Earned percentage]*20)

Detailed screenshot：

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Joseph Ji

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

Frequent Visitor

This is amazing.

Community Champion

It seems like you're trying to compute a score for each row in a Power BI table based on the earned percentage and then summing up those scores to get a total at the end of the table. Let's walk through the steps to achieve this:

1. Calculate Earned Percentage: You've already defined the Earned Percentage measure, which is good.

2. Calculate Score for Each Row: You've also defined the Score measure as [Earned percentage]*20.

3. Calculate Total Score at the Bottom: To get the sum of the scores at the bottom of the table, you can use the SUMMARIZE function combined with UNION in DAX.

Here's how you can do it step by step:

### Step 1 & 2:

You've already defined these measures. If not, you can create them in Power BI as follows:

Earned percentage = CALCULATE(
SUM('SLS3 InvoiceItem'[Carton]) / CALCULATE(SUM(Target[Target]))
)

Score = [Earned percentage] * 20

### Step 3: Calculate Total Score at the Bottom

To calculate the total score at the bottom, you can create a new table with the summarized values. Here's a DAX measure that you can add:

Total Score =
VAR SummaryTable =
SUMMARIZE(
'YourTableName', -- Replace 'YourTableName' with the name of your table
"Score", [Score]
)
VAR UnionTable =
UNION(
ALL(SummaryTable, SELECTCOLUMNS(SummaryTable, "Score", 0)),
SELECTCOLUMNS(SummaryTable, "Score", SUMX(SummaryTable, [Score]))
)
RETURN
MAXX(FILTER(UnionTable, [Score] = 0), [Score])

Replace 'YourTableName' with the actual name of your table in Power BI.

This measure will give you the total score of 130 at the bottom of your table. You can then drag this measure into your table visualization, and it should display the total score as 130.

Remember to replace 'YourTableName' with the actual name of your table in your Power BI model.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Frequent Visitor

I tried this several times but I don't know why it doesn't work for me.
I will send you the sample file. I would be grateful if you could check it.

Community Champion

ok plz w8.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors