Table showing column as percentage of another column

[Sir_Rosenfeld]

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?

[Ashish_Mathur]

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 %.

[v-yuta-msft · Accepted solution]

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

  

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

  

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

[SivaMani]

@Sir_Rosenfeld,

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