Combining dynamic information

[ryanmkan]

Hello,

I have data that is structured like this

CAT	PERIOD	AMOUNT
a	1	x
b	1	x
c	1	x
a	2	x
b	2	x
c	2	x
a	3	x
b	3	x
c	3	x
a	4	x
b	4	x
c	4	x

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 · Accepted solution]

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.