Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
JavidRobatian
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.

Screenshot 2023-12-26 090447.png

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

http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376 

1 ACCEPTED SOLUTION
v-jincheng-msft
Community Support
Community Support

Hi @JavidRobatian ,

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:

vjinchengmsft_0-1703580558070.png

 

 

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.

View solution in original post

5 REPLIES 5
v-jincheng-msft
Community Support
Community Support

Hi @JavidRobatian ,

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:

vjinchengmsft_0-1703580558070.png

 

 

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.

This is amazing.
Thank you for your help.

123abc
Community Champion
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.

Thank you for your answer
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.

https://drive.google.com/file/d/1kqsIMJjbORbinUcQ5Y_vfN5ka84ppIwq/view?usp=drive_link 

ok plz w8.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors