Percentage by Project and by month

[Anonymous]

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. 

[v-rzhou-msft · Accepted solution]

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.

[JuanVR10]

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]

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 !