topic Re: Total Sum of a measure in DAX Commands and Tips

Total Sum of a measure

JavidRobatian — Tue, 26 Dec 2023 05:46:13 GMT

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

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

Re: Total Sum of a measure

123abc — Tue, 26 Dec 2023 07:20:51 GMT

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:

Calculate Earned Percentage: You've already defined the Earned Percentage measure, which is good.
Calculate Score for Each Row: You've also defined the Score measure as [Earned percentage]*20.
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.

Re: Total Sum of a measure

JavidRobatian — Tue, 26 Dec 2023 08:03:18 GMT

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

Re: Total Sum of a measure

123abc — Tue, 26 Dec 2023 08:17:20 GMT

ok plz w8.

Re: Total Sum of a measure

v-jincheng-msft — Tue, 26 Dec 2023 08:50:32 GMT

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：

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.

Re: Total Sum of a measure

JavidRobatian — Tue, 26 Dec 2023 09:10:32 GMT

This is amazing.
Thank you for your help.