Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]))
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
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
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]))
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
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
This explanation is great, thank you!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |