Calculated Table with the same values as a Table Viz

[npatten]

Hi--

I'm trying to teach myself how calculated tables function. I'm trying to create a Calculated Table that consolidates totals the same way that I'm currently getting using the Table viszualization. The attached pictures shows two outcomes: on the left is the result of a DAX formula, the right is when i drag and drop column headings into the Table viz. Here's the DAX formula:

Table = SUMMARIZECOLUMNS(Received[Appeal Revenue Program], Received[Appeal Division], Received[ASAReceived])

Thank you for any help

PBI Tables

[cosborn1231]

Hi @npatten,

I'm a little unclear here. I would suspect the summarize function would work for you in this instance.

Is the data you are pulling in on the right hand side aggregated or is there anything different there?

Thanks for double checking on that,

Christian

[npatten]

Hi--

The table on the right hand side has the same columns as in the formula producing the table on the left. Basically, i just dropped the column headings into the table viz, and selected 'sum' on the ASAReceived. (ASA is appeal split amount, which is the equivalent of a monthly revenue/sales figure in the fundraising world). Aside from that, there isn't any aggregation going on behind the scenes. I've put the doc on OneDrive if you want to have a look: https://1drv.ms/u/s!AlU_gjUjfB2GgTfhNq3L2rhqEMVP

Thank you!

---

@cosborn1231 wrote:

Hi @npatten,

I'm a little unclear here. I would suspect the summarize function would work for you in this instance.

Is the data you are pulling in on the right hand side aggregated or is there anything different there?

Thanks for double checking on that,

Christian

---

[Vvelarde · Accepted solution]

hi @npatten

With your Summarize Column you are grouping the values of each 3 columns with unique values.

You need to change your Summarize Columns to:

Table = SUMMARIZECOLUMNS(Received[Appeal Division], Received[Appeal Revenue Program],"ASARECEIVED",Sum(Received[ASAReceived]))

[npatten]

Thank you @Vvelarde!

Would you will be willing to write a little bit about why it works? I'm unclear about how the the "ASRECEVED", SUM(Table[Column]) element works under the hood.

Thanks again!

Nathan

[Vvelarde]

Summarize is make a "resume" of your table grouping by columns that indicate.

Example:

Table

ColA          Colb              ColC

USA           Miami            10

USA           Portland         15

Peru           Lima              8

Peru           Lima             8

Peru           Cuzco          10

Russia         Moscow        7

If you applied SummarizeColumns(Table[ColA], Table[ColB], Table[ColC]) you're telling to made a resume of this columns not sum or any other expression.

The result is: (All the unique combinations of this 3 columns)

ColA          Colb              ColC

USA          Miami              8

USA          Portland          15

Peru           Lima               8    (In original was 2 two identical rows)

Peru           Cuzco            10

Russia        Moscow          7

If you want to sum Column C yo need to tell to SummarizeColumn to do it this

SummarizeColumns(Table[ColA], Table[ColB], "NAME OF NEW COLUMN" SUM(Table[ColC]))

The result is: (All the unique combinations of first 2 columns and Sum of Col 3)

ColA          Colb              NAME OF NEW COLUMN

USA          Miami              8

USA          Portland          15

Peru           Lima              16  (sum 8 + 😎

Peru           Cuzco            10

Russia        Moscow          7

I hope this help you

[npatten]

This explanation is great, thank you!!!