Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

sum, filter and grouping issue

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:

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

     

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    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:

    relationship.png

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

    direction.png

    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 )
)

    re.png

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        5 years ago

        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