Forum Discussion
Matrix: Measure Totals
Hi all,
I have placed the 'Actual Timesheets' measure shown below into a matrix value.
The total isn't correct? How can I get it to total correctly?
Hello HenryJS
What is throwing you off is the thinking that the total column has anything to do with the individual date columns. Your formula is calculating correctly in the context of no date:
If you want the total column to sum the amount from the individual days you will need the change it to a SUMX formula.
Actual Timesheets = SUMX ( VALUES ( <your date column here> ), [Unique Workers] - [Unique Workers] * [Holiday (%)] )There is no need for the ( ) around the [Unique Workers] * [Holiday (%)] part of the formula, multiplication is calculated before subtraction.
4 Replies
HenryJS add new measure and use that
New Measures = SUMX ( VALUES ( Table[Date Column] ), [Actual Timesheet] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hello HenryJS
What is throwing you off is the thinking that the total column has anything to do with the individual date columns. Your formula is calculating correctly in the context of no date:
If you want the total column to sum the amount from the individual days you will need the change it to a SUMX formula.
Actual Timesheets = SUMX ( VALUES ( <your date column here> ), [Unique Workers] - [Unique Workers] * [Holiday (%)] )There is no need for the ( ) around the [Unique Workers] * [Holiday (%)] part of the formula, multiplication is calculated before subtraction.
jdbuchanan71 Thanks for your help. That worked.
How can I change the value to a whole number? Currently it is showing as a decimal number to 2 points.
HenryJS yes you can either change as jdbuchanan71 or keep your existing measure and refer it in the new measure, logic is the same,
to change the format, select the measure, you will see measure tools on the menu and that's where you can change the decimal to 0 or change it to the whole number.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
