Column total SUM but row values stay constant

[francoisW]

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.

[vivek31]

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

[bhanu_gautam]

@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.

[francoisW]

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

[Anonymous · Accepted solution]

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.