Dynamic Table Calculation

[jayala]

Hello guys, I am here again looking for your help.

I have a dataset with a column called Company Group.

I would like to build a new table based on that column only with the distinct values and an additional row "Total". I need also another column as an Index.

This is what I tried but it didn't work.

VAR companies = UNION(CALCULATETABLE(DISTINCT(DB[Company Group])),{("TOTAL")})

VAR index = GENERATESERIES(1, DISTINCTCOUNT(DB[Company Group])+1)

VAR _all =

GENERATE(companies1, index)

return _all

My desired outcome is something like:

Company | ID

Company1 | 1

Company2 | 2

Company3 | 3

TOTAL | 4

Can you guys provide me some help?

Thank you in advance!

Sample data:

[Greg_Deckler]

Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[jayala]

Hi Greg, thanks for your reply.

Please find a screenshot in the main post with sample data.

[Anonymous · Accepted solution]

Hi @jayala ,

You can create a new table with the following formula:

newCompany = ADDCOLUMNS(DISTINCT('Company'[Company Group]),"Index",RANKX(ALL('Company'),'Company'[Company Group],,ASC,Dense))

Best Regards

Rena