SUM and Percentage measures

[lpw1986]

Relatively new to DAX and hitting a bit of a brick wall so hoping for some help from the community! I've been through previous posts and have attempted to apply that to what I am doing, but I'm not able to get the outcome that I was hoping for! 

I have a table "table1" with the following fields

Acorn_type_e6 provides a numerical value (between 1-65) to each postcode. 

I have created a new table ("table2") which has a new column "ACORN TYPE" with numbers 1-65

I now want to add 2 new calculated columns: 

1) Shows the total number of occurances for the column value "ACORN TYPE" within "acorn_type_e6"  (so this would be a countif in excel) 

2) Shows the percentage that "acorn type" is of the overall total

Its probably something really basic here, but I'm struggling! 

thanks in advance

[lpw1986 · Accepted solution]

@Gabry thats amazing thank you!   I thought it would be something simple 🙂 

Is there a way I can then add the totals based on a third/fourth table? 

So "Table3" has a lookup which says "Acorn_Type_e6" value corresponds to a group in "Table4" so I can get a total count and a total percentage in Table2 for everything under Group X in Table4?

[Anonymous · Accepted solution]

Thanks for the reply from Gabry.

Hi @lpw1986 , 

I added some details by creating simple data and relationships:

 

Create calculated columns:

Total Number Group A = CALCULATE(COUNTROWS('Table1'),'Table4'[Group]="Group A") 

Percentage Group A = DIVIDE([Total Number Group A],SUM('Table2'[Total Number Group A]))

Result:

 

You can refer to the following link to learn: Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[lpw1986 · Accepted solution]

@Gabry thats amazing thank you!   I thought it would be something simple 🙂 

Is there a way I can then add the totals based on a third/fourth table? 

So "Table3" has a lookup which says "Acorn_Type_e6" value corresponds to a group in "Table4" so I can get a total count and a total percentage in Table2 for everything under Group X in Table4?

[Gabry]

You are welcome.

Yes sure you can do It the same way via reletionship or also using look up, filter.

If you encounter any issue write here so we can help you

Have a nice weekend

[Gabry]

Hello @lpw1986 ,

did you set a relationship between the two tables? If yes, you can write this calculated columns.

Num occ=

COUNTROWS(RELATEDTABLE('Table 1'))

Percentage= DIVIDE(COUNTROWS(RELATEDTABLE('Table 1')), COUNTROWS('Table 1'))