cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Percentage by Project and by month

Hello, here is my problem:

I would like to display the percentage of time spent on each project over months. This is the result I get when I drag and drop my fields like this:

However, the problem is that for each category of project I get the percentage compared to the total of ALL the months cumulated. And what I would like to get is the total for all the categories of projects per month, as done ine Excel here:

Can someone help me with a Dax solution (I presume). I have already tried many things with summarize, filter, group by, but nothing gives me this result. Thanks a lot in advance for your answers.

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

I create a sample to have a test.

Data table:

Date table is a calculated table by Dax.

``Date = ADDCOLUMNS( CALENDARAUTO(),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))``

Relationship:

Measure:

``````Percentage by Project and by month =
VAR _TOTAL_PerMonth =
CALCULATE (
SUM ( 'Table'[Time spent] ),
VALUES ( 'Date'[MonthName] ),
ALL ( 'Table' )
)
VAR _CURRENTVALUE =
CALCULATE ( SUM ( 'Table'[Time spent] ) )
RETURN
DIVIDE ( _CURRENTVALUE, _TOTAL_PerMonth )``````

Result is as below.

Best Regards,
Rico Zhou

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

3 REPLIES 3
Community Support

Hi @Anonymous ,

I create a sample to have a test.

Data table:

Date table is a calculated table by Dax.

``Date = ADDCOLUMNS( CALENDARAUTO(),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))``

Relationship:

Measure:

``````Percentage by Project and by month =
VAR _TOTAL_PerMonth =
CALCULATE (
SUM ( 'Table'[Time spent] ),
VALUES ( 'Date'[MonthName] ),
ALL ( 'Table' )
)
VAR _CURRENTVALUE =
CALCULATE ( SUM ( 'Table'[Time spent] ) )
RETURN
DIVIDE ( _CURRENTVALUE, _TOTAL_PerMonth )``````

Result is as below.

Best Regards,
Rico Zhou

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

Frequent Visitor

Hi friend!

i saw this response and is exactly what i´ve looking for, i have the same problem.

Now, this works for me, but only when my only filter is the year. When i apply another filter like, product category or more the graph doesn´t work.

Here an example:

Only selected 2022.

But when i selec 2022, and one Product category it shows this, and not the cumulative to reach the 100%

I have a calendar table related to the sales, and other table related with the product category.

how can i solve this?

Thanks!

Anonymous
Not applicable

It's exactly what I needed. Sorry for the late answer but I was in holiday and not in my work office. Thank you for your contribution !

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors