Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Calculate sum issue : X projects = 0 charges

Good morning !

I work on 800 projects that are charged with FTE (full time equivalent) by managers and dispatched between 50 files (for the 50 managers).

Some projects have 0 FTE as a total, while some projects have some contributions by some manager but not all of them.

 

I would like to calculate the number of missing contribution by managers.

For exemple : - Project 1 = 5 missings contributions. Indicates that the teams are the following (X1 ; X2 ; ...)

 

I have tried to do it in the following file, first page "Contributeurs manquants par Entités" with the formula :

"N-projets non-chargés = CALCULATE(COUNT('Charges'[N_Projet]), FILTER(Charges,[SOMETPS22020]=0))"

 

But what I get are a huge amount of projects, that are not correct. I fear that it is counting the number of 0 in my core files where the 0 appears to a maximum of 66 times per projects because it is expressed per months (66 months), and I want to know if a project has 0 charge as a total of those 66 months.

 

Here is the link to my file : https://www.swisstransfer.com/d/ac8d5573-55a0-402d-8ce4-9d6ddaccb21e 

 

Please do tell me if I was not clear enough or need to imput more information. I'm continuing to search for a solution and will put the solution if I finaly find it elswhere.

Thank you very much for your appreciated help !

Best regards,
Marc

 

2 ACCEPTED SOLUTIONS
AlB
Community Champion
Community Champion

@Lotam

Try this new version. Something complicated was going on. The result is now 4 for the mentioned case:

N-projets non-chargés V4 =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'Charges'[N_Projet] ),
            Charges[Itération_PdC] = "S2-2020"
        ),
        CALCULATE ( SUM ( Charges[Charges] ) = 0 )
    )
)

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

SU18_powerbi_badge

View solution in original post

AlB
Community Champion
Community Champion

@Lotam

I'm sorry I couldn't answer sooner. Try this new measure that uses the one we already had:

N-projets non-chargés V4_TOT =
SUMX ( DISTINCT ( Charges[Groupe_Contributeur] ), [N-projets non-chargés V4] )

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

SU18_powerbi_badge

View solution in original post

13 REPLIES 13
amitchandak
Super User
Super User

@Anonymous , Try a measure like

countx(filter(summarize('Charges','Charges'[N_Projet],"_1",[SOMETPS22020]),[_1]=0),[N_Projet])
or
countx(filter(summarize('Charges','Charges'[N_Projet],"_1",[SOMETPS22020]+1),[_1]=0),[N_Projet])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hello @amitchandak ,

Thank you for your answer.

I tried both calculation, but It did not fit what it should look like. As I have a great difference between what PBI shows me and the reality (160 projects with 0 FTE, but my files will show me 11).

Still, thank you very much, I'll keep looking and using your calculation to try to find my answer.

Crowd

AlB
Community Champion
Community Champion

Hi @Anonymous 

What is the expected result? I don't completely understand the logic for the calculation but try this:

 

N-projets non-chargés V3 = COUNTROWS(FILTER(DISTINCT('Charges'[N_Projet]), [SOMETPS22020]=0))

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

I think I found out but I am unsure on how to correctly do it in the formula.

PowerBI may not being rounding up some numbers. The total may be of 0,4 and PowerBI counts it as a 0.

I don't seem to be able to use the Roundup formula correctly (if it is this one).

Plus, I also think PowerBI counts my first row (which is the headtitle of my board in excel) as one project being 0.

Do you know how to counter this?

Thank you,
Crowd

Anonymous
Not applicable

It seems I keep trying to answer you and the forum denies it !

Hello @AlB ,

Thank you for your answer.

I am trying to count the number of projects with 0 FTE so I can have a good picture of the amount of work for each manager that still has to be done.

Your solution works, but not perfectly as I have a difference of 2 or 3 in my results :
Expected 16

PowerBI : 18

Does that mean that Countrows is counting additionnal rows? But I can't seem to find out where or why. I will keep looking.
If you have the answer, please do enlight me !

Thank you again,

Crowd

AlB
Community Champion
Community Champion

@Anonymous 

Where/how are you using the my measure? If i place it in a card visual in the file you shared I get a result of 406. Where do you get the 18?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

Sorry, if its in a card its indeed 406 as a total.

18 was from one of the files out of 50 (easier to count).

Your answer is the best one I have, even if its not 100% accurate.
I still don't know why its not. Maybe it should be a Distinctcount?

Regards,

Crowd

AlB
Community Champion
Community Champion

@Anonymous 

What file are you looking at? Either send me a pbix with the scenario you have now (with 18 as result) or tell me how to build it with the file you shared earlier. Then I can have a look.

By the way why are some of the numbers on the "Itération_PdC" so small?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Anonymous
Not applicable

@AlB

For example, in the PBI file, I have in "Contributeurs manquants par Entités" a blue square called "DEP" with a display in ring in it. There is "PLM" in it and when over it it displays 7.

In the Excel file, it is "DEP_DPI_PLM". I have put a sum of each rows at the end of them. I only have 4 with a total of 0 (in red).

So there is a discrepancy of 3.

Some numbers in the "Itération_PdC" are very small because of the way I need it to be input (for further extraction for entities of my employer).


Gratefuly

Crowd

https://www.swisstransfer.com/d/c3829a86-7617-48f2-8362-d64613c073e3

AlB
Community Champion
Community Champion

@Lotam

Try this new version. Something complicated was going on. The result is now 4 for the mentioned case:

N-projets non-chargés V4 =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'Charges'[N_Projet] ),
            Charges[Itération_PdC] = "S2-2020"
        ),
        CALCULATE ( SUM ( Charges[Charges] ) = 0 )
    )
)

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

SU18_powerbi_badge

Anonymous
Not applicable

Thank you very much @AlB !

This does it very nicely. With your solution, a collegue of mine and I tried to complete it just a bit with a visualisation to sum up the numbers of rows with 0 charges.

For example, for each boxes (DEP, MEI, PPI, MSP and DIR) have the total of all rows with 0.
DEP should be 26+15+11+6+4+4+2+1 =68.

Unfortunatly we stalled while trying the following :

N-unsered projects V5
COUNTROWS (
FILTER(
IF(AND (Charges[Itération_PdC] = "S2-2020", Charges[Groupe_Contributeur]="IST"
),(CALCULATETABLE (
DISTINCT ( 'Charges'[N_Projet] )))),
CALCULATE ( SUM ( Charges[Charges] ) = 0 )
)
)

Would you have an idea?

Again thank you, I have marked the post as having being solved !

Best regards,
Crowd

AlB
Community Champion
Community Champion

@Lotam

I'm sorry I couldn't answer sooner. Try this new measure that uses the one we already had:

N-projets non-chargés V4_TOT =
SUMX ( DISTINCT ( Charges[Groupe_Contributeur] ), [N-projets non-chargés V4] )

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

SU18_powerbi_badge

Anonymous
Not applicable

@AlB Thank you again, my colleague and I were dumbfounded by the simplicity ! It works like a charm.

Thank you again !

Regards, Lotam

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.