Equivalent of SOMMEPROD

[Anonymous]

Hello experts,

I want to calculate in the Data table and not in the Report part.

In the table given for example, with several arguments (if, or, and, sumprod ...) I arrive at: - For a Subsystem: determine a "Fait" or "RaF" status. - For a System: assemble the state of its subsystems and return "Fait" if they are all done, "Partiel" if only some are done, and finally "RaF" if nothing has been done (via Task, System, Sub System and implementation data)

for your translation (partiel = partial / Fait = done / RaF = still to do)

example of what i want :

Actualy in power bi

thank !!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

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

Table:

You may create two calculated columns as below.

Sub Sys Status = 
IF(
    ISBLANK([Date]),
    "RaF",
    "Fait"
)

Sys Status = 
var num = 
COUNTROWS(
    FILTER(
        'Table',
        [Travail]=EARLIER('Table'[Travail])&&
        [System]=EARLIER('Table'[System])&&
        NOT(ISBLANK([Date]))
    )
)
var totalnum = 
COUNTROWS(
    FILTER(
        'Table',
        [Travail]=EARLIER('Table'[Travail])&&
        [System]=EARLIER('Table'[System])
    )
)
return
SWITCH(
    TRUE(),
    num=0,"RaF",
    num<totalnum,"Partiel",
    num=totalnum,"Fait"
)

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.