Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
jayala
Frequent Visitor

Dynamic Table Calculation

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:
Screenshot_2.png

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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))

newCompany.JPG

Best Regards

Rena

 

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi Greg, thanks for your reply.

 

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

 

 

Anonymous
Not applicable

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))

newCompany.JPG

Best Regards

Rena

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors