Forum Discussion

ryanmkan's avatar
ryanmkan
Regular Visitor
2 years ago
Solved

Combining dynamic information

Hello,

 

I have data that is structured like this

CATPERIODAMOUNT
a1x
b1x
c1x
a2x
b2x
c2x
a3x
b3x
c3x
a4x
b4x
c4x

 

The user is able to select period ranges for category A and B, and then seperately for category C. 
Example, sum the amounts for category A and B from period 1-2, and for category C from period 3-4.

There are no rules to offset the period, it is purely down to selection.

I want to be able to calculate the amounts per category, but then recombine it all into one table so that other columns (category grouping, and higher hierarchical information) can be displayed in a single matrix.

Am really stuck!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ryanmkan 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    Create measures.

     

     

    Measure = 
IF(
    SELECTEDVALUE('Table'[CAT]) = "a" 
    || 
    SELECTEDVALUE('Table'[CAT]) = "b",
    CALCULATE(
        SELECTEDVALUE('Table'[AMOUNT]),
        FILTER(
            'Table', 
            'Table'[CAT] in {"a", "b"} && 'Table'[PERIOD] in {1, 2}
        )
    ),
    CALCULATE(
        SELECTEDVALUE('Table'[AMOUNT]), 
        FILTER(
            'Table', 
            'Table'[CAT] = "c" && 'Table'[PERIOD] in {3, 4}
        )
    )
)

     

     

     

    Measure 2 = SUMX('Table', 'Table'[Measure])

     

     

    Create a matrix.

     

           

     

     

    Regards,

    Nono Chen

    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
    2 years ago

    Hi ryanmkan 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    Create measures.

     

     

    Measure = 
IF(
    SELECTEDVALUE('Table'[CAT]) = "a" 
    || 
    SELECTEDVALUE('Table'[CAT]) = "b",
    CALCULATE(
        SELECTEDVALUE('Table'[AMOUNT]),
        FILTER(
            'Table', 
            'Table'[CAT] in {"a", "b"} && 'Table'[PERIOD] in {1, 2}
        )
    ),
    CALCULATE(
        SELECTEDVALUE('Table'[AMOUNT]), 
        FILTER(
            'Table', 
            'Table'[CAT] = "c" && 'Table'[PERIOD] in {3, 4}
        )
    )
)

     

     

     

    Measure 2 = SUMX('Table', 'Table'[Measure])

     

     

    Create a matrix.

     

           

     

     

    Regards,

    Nono Chen

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