topic Re: Column total SUM but row values stay constant in DAX Commands and Tips

Column total SUM but row values stay constant

francoisW — Thu, 30 Jan 2025 09:52:35 GMT

Hi im having trouble with something seemingly simple, i need the values of my rows to stay constant, whilst having them sum up to a total at the end of the table.

I have a Montly_target, which is define as Daily_target * Days_in_Month.

Days_in_month = 25,
Daily_Target = 1

As shown in the graph it is hardcoded to be 25 for every row, but i need the column to display the SUM of the column, without altering the individual row values.

Please let me know if i need to enter more information, this is my first question.

Re: Column total SUM but row values stay constant

bhanu_gautam — Thu, 30 Jan 2025 10:18:13 GMT

francoisW , Create a measure

Total_Monthly_Target = SUM('YourTable'[Monthly_target])

In your table visual, add the Total_Monthly_Target measure. This will show the sum of the Monthly_target column without altering the individual row values.

Re: Column total SUM but row values stay constant

francoisW — Thu, 30 Jan 2025 11:06:15 GMT

If i understood you correctly this is my output from your solution. Its not entirely what im looking for, its not supposed to be such a high number, im looking for around 30 000. It should only count each row's value once, so I might have made a mistake, but the 38125 should not be there.

Im looking for a function that will keep vMonthly_Targets row value at 25 and only displaya total at the end of the column

Re: Column total SUM but row values stay constant

vivek31 — Thu, 30 Jan 2025 11:29:39 GMT

HI francoisW ,

you can add calculate colum in your table

Monthly_Target = SUM('target table'[Daily_target]) * SUM('target table'[Days_in_Month])

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you

Re: Column total SUM but row values stay constant

Anonymous — Fri, 31 Jan 2025 08:32:44 GMT

Thanks for the reply from vivek31 and bhanu_gautam , please allow me to provide another insight:

Hi, francoisW
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

Measure = IF ( ISINSCOPE ( 'Table (2)'[Column1] ), [Daily_target1] * [Days_in_Month1], SUMX ( 'Table (2)', [Daily_target1] * [Days_in_Month1] ) )

3.Here's my final result, which I hope meets your requirements

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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