Forum Discussion

sheap069's avatar
sheap069
Icon for Helper III rankHelper III
6 years ago
Solved

Get Column Percentage and Line Chart Values as Percentage

Hello,   I have 25 regions and I'm currently displaying the count of applications made to each region based on time in a line chart, as shown in the following image.  This is working the way...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, sheap069 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    You may create a calculated column and two measures as below.

    Calculated column:
YearMonth = FORMAT('Table'[Date],"yyyy-mm")

Measure:
Percentage for each region = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region])
    )
)

Percentage of applications for each region for each month = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region],'Table'[YearMonth])
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, sheap069 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Table:

 

You may create a calculated column and two measures as below.

Calculated column:
YearMonth = FORMAT('Table'[Date],"yyyy-mm")

Measure:
Percentage for each region = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region])
    )
)

Percentage of applications for each region for each month = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Region],'Table'[YearMonth])
    )
)

 

Result:

 

Best Regards

Allan

 

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

sheap069's avatar
sheap069
Icon for Helper III rankHelper III
6 years ago

Hi v-alq-msft 

 

Thank you very much for this, it works!. If I want to repeat this with years and days, I assume that I would repeat the same logic. I'm also looking to add the percentages of applications for year, day of the month (where I will have a calculated column) and the day of the week.

 

Thank you again!

 

Edit: I've actually tried with Day of Week but this doesn't seem to be working. Is it because I would need this to be a calculated column as well? I currently have it as a Text Column extracted from the Application Date, Sunday-Saturday.