cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sir_Rosenfeld Occasional Visitor
Occasional Visitor

Table showing column as percentage of another column

Hi,

 

I have a table that looks like this

|          Date             |   Total  |   Loss  |
|---------------------|---------|--------|
|      Jan 1 2018       |    90      |    5     |
|---------------------|---------|--------|
|     Jan 3 2018         |    10     |    5      |
|---------------------|---------|--------|
|      Feb 1 2018       |    50     |      5    |
|---------------------|---------|--------|
|      Feb 3 2018       |    50     |    10    |
|---------------------|---------|--------|

I wanna be able to show in a table how much percentage of "Total" "Loss" is depending on the period.

For example if in my dashboard I show the table visualizer as months, it should give :

|        Date        |  Loss % |
|----------------|----------|
|    Jan 2018     |   10%     |
|----------------|----------|
|    Feb 2018     |   15%    |
|----------------|----------|

But if I show it as year, it should give :

|    Date   | Loss % |
|----------|---------|
|   2018   |  12.5%  |
|----------|---------|

 

Right now the only solution I have found is to create tables in the Data tab that would sum up my main table for each Month. But that can't be a permanent solution to me (I end up with too many tables, as I have to do that kind of table for many different columns and conflicting relationships). Maybe a custom visualizer? Any alternative solution?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Table showing column as percentage of another column

Hi Sir_Rosenfeld,

 

So you need column date in the first column of new table to be dynamic based on something you selected, right? If that's your requirement, I'm afraid neither dynamic calculate table nor dynamic calculate column is supported in power bi. However, as a workaround, you can create new tables using dax formula below:

Table =
SUMMARIZE (
    Table1,
    Table1[Date].[Year],
    Table1[Date].[Month],
    "Date", LEFT ( Table1[Date].[Month], 3 ) & " "
        & Table1[Date].[Year],
    "Loss%", SUM ( Table1[Loss] ) / SUM ( Table1[Total] )
)

 

1.PNG  

 

Table 2 =
SUMMARIZE (
    'Table1',
    Table1[Date].[Year],
    "Date", Table1[Date].[Year],
    "Loss%", SUM ( Table1[Loss] ) / SUM ( Table1[Total] )
)

2.PNG  

 

In addtion, if you don't want the first column to be dynamic, I would recommend you to create an addtional table selection and then create a measure like pattern below:

 

Loss% = 
IF (
    SELECTEDVALUE ( Selection[Selection] ) = "Year",
    CALCULATE (
        SUM ( Table1[Loss] ) / SUM ( Table1[Total] ),
        ALLEXCEPT ( Table1, Table1[Date].[Year] )
    ),
    IF (
        SELECTEDVALUE ( Selection[Selection] ) = "Year&Month",
        SUMX(ALLEXCEPT(Table1, Table1[Date].[Year], Table1[Date].[Month]), Table1[Loss] / SUMX(ALLEXCEPT(Table1, Table1[Date].[Year], Table1[Date].[Month]), Table1[Total]))
    )
)

 

Regards,

Jimmy Tao

3 REPLIES 3
SivaMani Senior Member
Senior Member

Re: Table showing column as percentage of another column

@Sir_Rosenfeld,

 

What is the datatype of Date field? or Do you have any field with Date datatype?

 

Community Support Team
Community Support Team

Re: Table showing column as percentage of another column

Hi Sir_Rosenfeld,

 

So you need column date in the first column of new table to be dynamic based on something you selected, right? If that's your requirement, I'm afraid neither dynamic calculate table nor dynamic calculate column is supported in power bi. However, as a workaround, you can create new tables using dax formula below:

Table =
SUMMARIZE (
    Table1,
    Table1[Date].[Year],
    Table1[Date].[Month],
    "Date", LEFT ( Table1[Date].[Month], 3 ) & " "
        & Table1[Date].[Year],
    "Loss%", SUM ( Table1[Loss] ) / SUM ( Table1[Total] )
)

 

1.PNG  

 

Table 2 =
SUMMARIZE (
    'Table1',
    Table1[Date].[Year],
    "Date", Table1[Date].[Year],
    "Loss%", SUM ( Table1[Loss] ) / SUM ( Table1[Total] )
)

2.PNG  

 

In addtion, if you don't want the first column to be dynamic, I would recommend you to create an addtional table selection and then create a measure like pattern below:

 

Loss% = 
IF (
    SELECTEDVALUE ( Selection[Selection] ) = "Year",
    CALCULATE (
        SUM ( Table1[Loss] ) / SUM ( Table1[Total] ),
        ALLEXCEPT ( Table1, Table1[Date].[Year] )
    ),
    IF (
        SELECTEDVALUE ( Selection[Selection] ) = "Year&Month",
        SUMX(ALLEXCEPT(Table1, Table1[Date].[Year], Table1[Date].[Month]), Table1[Loss] / SUMX(ALLEXCEPT(Table1, Table1[Date].[Year], Table1[Date].[Month]), Table1[Total]))
    )
)

 

Regards,

Jimmy Tao

Super User
Super User

Re: Table showing column as percentage of another column

Hi,

 

Create a Calendar Table and build a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, create 2 additional columns for Month and Year

 

Month=FORMAT(Calendar[Date],"mmmm")

Year=Year(Calendar[Date])

 

In the visual, drag Year and/or Month to your visual (from the Calendar Table).  Write these measures:

 

=SUM(Data[Loss])/SUM(Data[Total])

 

Format this as %.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 132 members 2,024 guests
Please welcome our newest community members: