Forum Discussion

Sander1401's avatar
Sander1401
Helper II
3 years ago
Solved

Help needed with measure

Hi all,

 

I need help with a simple measure, but I don't get it working...
I have a dataset with Producttype - current month and Producttype - last month and numbers.
It shows the number of customers going from Producttype - lastmonth into Producttype - current month.
Now I want to built an overview which shows a measure with the aggregated numbers per Producttype - current month and the aggregated numbers per Producttype - last month.

 

See for an example below:

Producttype - current monthProducttype - last monthCount
AA - B29
AA - C2.882
AA - D1.618
AA - D - B3
AA - D - C4.843
AA - D - C - B3
AD56
A - BA1.142
A - BA - C6
A - BA - C - B3
A - BA - D6
A - BA - D - B41
A - BA - D - C6
A - BA - D - C - B30
A - BD - B1
A - CA2.734
A - CA - C - B5
A - CA - D42
A - CA - D - C769
A - CD3
A - C - BA3
A - C - BA - B4
A - C - BA - C376
A - C - BA - D - C4
A - C - BA - D - C - B13

 

Producttype - current monthCount - IN
A9.434
A - B1.235
A - C3.553
A - C - B400

 

Producttype - last monthCount - OUT
A3.879
A - B33
A - C3.264
A - C - B8

 

I've made a combination of calculations but now I only have the total in every row and not every row specified.

 

COUNT - IN = 
CALCULATE(
        SUM([COUNT]),
        'Table'[Producttype - current month] = "A",
        'Table'[Producttype - last mont] <> "A"
        )
        +          
    CALCULATE(
        SUM([COUNT]),
        'Table'[Producttype - current month] = "A - B",
        'Table'[Producttype - last mont] <> "A - B"
        )  
        +  
       etc....
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Sander1401 ,

     

    I can probably understand your idea from the formula you provided, but I don't correspond to your desired result. I don't know what you expect, such as 9.434 is calculated from the sample data.

    For now, you may try this measure

    COUNT - IN =
CALCULATE (
    SUM ( 'Table'[Count] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Producttype - current month] = MAX ( 'Table'[Producttype - current month] )
            && [Producttype - current month] <> [Producttype - last month]
    )
)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Sander1401 ,

     

    I can probably understand your idea from the formula you provided, but I don't correspond to your desired result. I don't know what you expect, such as 9.434 is calculated from the sample data.

    For now, you may try this measure

    COUNT - IN =
CALCULATE (
    SUM ( 'Table'[Count] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Producttype - current month] = MAX ( 'Table'[Producttype - current month] )
            && [Producttype - current month] <> [Producttype - last month]
    )
)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.