Forum Discussion
Variance column for Dynamically selected columns
Greetings,
I have a matix that consist of dynamic columns. The data consist of reports that are Jan-Dec. Each month has it's own Jan thru Dec totals in each file, see below left side. Currently I am testing Jan-Mar first. I would like for whichever columns are selected by the user to equal a measure which is the "variance" column. Is this possible? See below picture. Any assistance would be greatly appreciated. I need a "variance" column to replace the "Total" column.
Thanks everyone. I found it on another thread. I found this code and used it, but it only worked once I have a sum total next to it.
var mmax = CALCULATE(max(DataProfile[Units]),allselected(Contacts[Name])) var mmin = CALCULATE(min(DataProfile[Units]),allselected(Contacts[Name])) return mmax-mmin
I will come return to give credit. Thanks.
8 Replies
Option 1. Remember that your measure is calculated four times in a matrix visual. Change the formula for the column totals to variance
Option 2. Alternatively consider using visual calculations.
Thank you, I will try that and get back to you. Besides just changing the option to variance which looks crazy, it gives me zeros.
yeah, that's not going to work. I need a variance for 2 selected months. I'll just let him know that is the limit and makes sense.
Hi,
So if you have selected 3 months (as shown in the picture), then how should variance be calculated?
I am suggesting to my client that they only compare 2 at a time or do a visual. Two is difficult enough,also it would make any sense to try to get a variance from 3 or more.
Thank you, I have posed that very question to him, I am awaiting feedback. 🙂
Thanks Ashish. I need a variance for 2 selected months. I'll just let him know that is the limit and makes sense.
Thanks everyone. I found it on another thread. I found this code and used it, but it only worked once I have a sum total next to it.
var mmax = CALCULATE(max(DataProfile[Units]),allselected(Contacts[Name])) var mmin = CALCULATE(min(DataProfile[Units]),allselected(Contacts[Name])) return mmax-mmin
I will come return to give credit. Thanks.
His user name is "Ibenlin".
