CODEWITHSUNDEEP
bigdatapowerbibusiness-intelligencepowerbi-desktop
cildoz
cildoz

Reputation: 436

Calculating new columns in PowerBI

I've got this table I've defined in PowerBI:

enter image description here

I'd like to define a new table which has the percentage of medals won by USA from the total of medals that were given that year for each sport.

An example:

Year Sport Percentage

1986 Aquatics 0.0%

How could I do it?

Upvotes: 0

Views: 54

Answers (2)

Joe G
Joe G

Reputation: 1776

I know that an answer has already been accepted, but I feel that I should provide my suggested solution to utilize all of Power BI's capabilities.

By creating a calculated table, you are limited in what you can do with the data, in that it is hard coded to be filtered to USA and is only based on Year and Sport. While that is the current requirements, what if they change? Then you have to recode your table or make another one.

My suggestion is to use measures to accomplish this task, and here's how...

First, here is my set of sample data.

Sample Data

With that data, I created a simple measure that count the rows to get the count of medals.

Medal Count = COUNTROWS(Olympics)

Throwing together a basic matrix with that measure we can see the data like this.

basic matrix

A second measure can then be created to get a percentage for a specific country.

Country Medal Percentage = DIVIDE([Medal Count], CALCULATE([Medal Count], ALL(Olympics[Country])), BLANK())

Adding that measure to the matrix we can start to see our percentages.

expanded matrix

From that matrix, we can see that USA won 25% of all medals in 2000. And their 2 medals in Sport B made up 33.33% of all medals that year.

With this you can utilize slicers and the layout of the matrix to get the desired percentage. Here's a small example with a country and year slicer that shows the same numbers.

slicer matrix

From here you are able to cut the data by any sport or year and see the percentage of any selected country (or countries).

Upvotes: 1

OscarLar
OscarLar

Reputation: 1335

You can use SUMMARIZE() to calculate a new table:

NewTable = 
SUMMARIZE(
    yourDataTable;
    [Year];
    [Sports];
    "Pct"; 
    DIVIDE(
        CALCULATE(
            COUNTROWS(yourDataTable);
            yourDataTable[Nat] = "USA"
        );
        CALCULATE(
            COUNTROWS(yourDataTable);
            ALLEXCEPT(
                yourDataTable;
                yourDataTable[Year];
                yourDataTable[Sports]
            )
        );
    0
)

Upvotes: 1

Related Questions