Adding calculated columns to matrix visual

[Anonymous]

I'm trying to figure out how to add calculated columns to a matrix visual. I have a data set in excess of 10M records. Via a series of slicers the data set gets reduced to records of interest. Those records are aggregated into a very simple matrix visual (example shown below). 

I'm trying to figure out how to add a calculated column to that matrix visual. Each National Estimate would be used to make 3 new cacluations. I need the whole table to be interactive and adjust with the slicers that feed the matrix.

Addmittely I am new to PowerBI and I hope this is as simple as I think it should be.

I appreciate your insights.

Dean

Matrix visual

Year	National Estimate	Calcuated Column
2017	14,727,704
2010	14,694,928
2012	14,614,128
2016	14,319,418
2011	14,162,084
2015	14,132,697
2013	14,033,745
2009	13,966,898
2014	13,860,971
2008	13,456,353
2001	13,310,088
2007	13,232,338
2006	13,232,263
2004	13,096,983
2000	12,924,488
2002	12,790,786
2003	12,720,947
2005	12,608,693
1999	12,165,225
1998	12,049,690

[MFelix]

Hi @Anonymous ,

I'm assuming that when you refer to calculated columns is a new column on the matrix and not on the data correct?

Just as a reference when you refer to calculated columns in PBI we are refering to columns that are added to the dataset tables and not on the visuals, this columns can then be used on the visuals. This calculated columns will increase the size of your file off course.

In your case if you want to add information to your matrix based on the slicers selection you need ot add a measure, however this has different ways of calculating than a column since for the use of measures you need a summarization formula.

Example of a measure is 

Total Estimate = SUM(Table[National Estimate])

this will give you the same total as you have the estimate column but the important part of this is that I have to use the SUM formula to get a result based on context, that ocntext comes from filters, visual, information within the measures.

Please check this article with further explanation on columns and measures.

If you need assistance can you please share some more information about what you want to achieve, sample data and expected result.

Regards,

MFelix

[Anonymous]

Yes, I want to add new columns on the matrix and not on the data correct?

I neet to take those National Estimates and perform and report an algebraic calculation for each. The first is a complex equation that is a curve fit for a government graph. From that comes two simpler calculation using the National Estimate and that new calculation.

The whole of the effort is intended to eliminate a manual step of using a graph.

[Anonymous]

@Anonymous ,

What would the three calculated columns be?  Though, since the original table is being filtered down you would probably want these to be measures. But one step at a time, if you could list out what the 3 calculations are that would be extremely helpful.

[Anonymous]

I was seeking to keep it as simple as possible. I figure any simple algebraic example would be sufficient to get me. In reality I'll be using  a complex equation that apporoximates a curve from a government graph. The formula uses the the calculated National Estimate divided by1000 and then an exponent and natural log type equiation. From that I have two more simple algebra calculations to make and report.  The ultimate goal is to calculate what is now a manual lookup from a published graph.

Thanks for you question. If you can Help, I'd be super greatful!

[v-juanli-msft]

Hi @Anonymous 

You could create measures and add them in the Value field.

For exponent and natural log type equiation, you could use EXP and LOG function.

Learn syntax here : EXP function, LOG function

Note that you might receive an error if the value is too large or too small.

Measure_divide = SUM(Sheet2[National Estimate])/1000

Measure_exp = EXP([Measure_divide]/1000)

Measure_log = LOG([Measure_divide])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Unfortunately the "National Estimate" numbers aren't sitting conveniently in a table as in the example. In my actual data, the "National Estimate" is a calcuated column of a in a Matrix visual. The visual gets calculated as a function of multiple slicers. I'm seeking to do additional columns based upon the results shown in the matrix visual.