Forum Discussion

Zkna-M's avatar
Zkna-M
Frequent Visitor
9 years ago
Solved

running sum grouped by probablity

I am trying to add a new measure that can calulate cumulative total of value of an item and summarise it on based on probablity.

This is the data that I have

 

ItemValueProbablity
4640.9
4760.8
2420.0
1930.9
1840.8
2850.1
1990.2
1550.1
2911.0
2470.5
2710.7
3800.4

 

So if I summarise by probablity 0.8 and item 1, it should give me the sum of value for all probablity greater than 0.8

 

This is the report that I want to create.

Item 0.10.20.30.40.50.60.70.80.91
1331276177177177177177177930
2294209209209209162162919191
380808080000000
4140140140140140140140140640

 

Can anyone help me out.

  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Zkna-M

     

    hi friend

     

    First, Create a new Table (Modeling Enter Data)

     

     

    2. Related Both Table 

     

    3. Create a New Measure

     

    SumValues =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLEXCEPT ( Table1; Table1[Item] ),
        Table1[Probablity] >= MAX ( Probabilities[Probability] )
    )
)
    + 0

    4. Ready. You can view in a Matrix

     

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Zkna-M

     

    hi friend

     

    First, Create a new Table (Modeling Enter Data)

     

     

    2. Related Both Table 

     

    3. Create a New Measure

     

    SumValues =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLEXCEPT ( Table1; Table1[Item] ),
        Table1[Probablity] >= MAX ( Probabilities[Probability] )
    )
)
    + 0

    4. Ready. You can view in a Matrix

     

    • Zkna-M's avatar
      Zkna-M
      Frequent Visitor
      9 years ago

      I tried but it is not working for me. I must be missing something. 

      This is the measure that I have: 

       

      SumValue = 
CALCULATE(
	SUM('Item'[Value]),
	FILTER( ALLEXCEPT('Item','Item'[Item]),
		'Item'[Probability] <= MAX(Probablities[Probability])
	)
)
+ 0

       

      And this the matrix that I get: 

       

      What am I missing? 

       

       

       

      • v-sihou-msft's avatar
        v-sihou-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Zkna-M

         

        It should be "greater than" (>=) the custom porbability in filter context:

         

        SumValue = 
CALCULATE(
	SUM('Item'[Value]),
	FILTER( ALLEXCEPT('Item','Item'[Item]),
		'Item'[Probability] >= MAX(Probablities[Probability])
	)
)
+ 0

        Regards,