sum, filter and grouping issue

[Anonymous]

Hello,

i am a beginner, i would be thankfull, if you could help me to undrestand what is my mistakes in this report, i looked up but i couldnt undrestand how to solve it:

in the fist table, „soll“ ist the quantity which is shown each month.

In the second table, I want to have „soll“ quantity according to each „plannungressourcenr.“

In third table, i want to see „soll“ quantity for each „ressourcenr“.

But in second and third table, it is showing the same total amount for „soll“ from first table.

I created a measure for soll according to the following formular and it is used in all tables.

M sollmenge = CALCULATE(

SUM('Projekt-Ressourcen-Zeit Posten'[Menge])

,FILTER(

ALL('Projekt-Ressourcen-Zeit Posten'[Erfassungsart])

,'Projekt-Ressourcen-Zeit Posten'[Erfassungsart] = "soll"

)

)

relation between my tables  are:

[v-yingjl]

Don't @MahsaR,

According to my test, the reason they cause this problem is that the cross-filter address between the date table and the message table is unique instead of both:

To resolve this, you can change the direction from one to both directly:

Or use the Crossfilter() function to modify its measurement:

soll =
CALCULATE (
    SUM ( 'Projekt-Ressourcen-Zeit Posten'[Menge] ),
    FILTER (
        ALL ( 'Projekt-Ressourcen-Zeit Posten'[Emfassungsart] ),
        'Projekt-Ressourcen-Zeit Posten'[Emfassungsart] = "soll"
    ),
    CROSSFILTER ( 'Date'[Date], 'Post Ressource Kapazitat'[Date], BOTH )
)

Attached a sample file in the next one, hopes to help you.

Best regards
Community Support Team _ Yingjie Li
If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

i used CROSSFILTER but it is not having correct answer. it gives me following result, which is still wrong, because i need to see how  the total quantity of 1350 is spread between plannungressources.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Have you tried to change the filter direction in the relationship view directly?

If still not works, could you please consider sharing a simple sample file without any sesentive information for further discussion?

Best Regards,
Community Support Team _ Yingjie Li

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

M sollmenge = CALCULATE(
SUM('Projekt-Ressourcen-Zeit Posten'[Menge])
,FILTER(
('Projekt-Ressourcen-Zeit Posten'[Erfassungsart])
,'Projekt-Ressourcen-Zeit Posten'[Erfassungsart] = "soll"
)
)

Try like