Problem with showing objects that have data in a given period and calculate the percentage change

[nor303]

Hi, 

I want to see whichs object that have data for a given period. Then I want to see the percentage change on the total and also see have many objects thats increase or decrease in value or are unchanged. The data looks like this and my preferred output. I will show this in Powerbi with maybe a matrix and som charts and cards. Any suggestions on how to solve this? Ive looked at both youtube and chatgtp without luck. (In the tables I want need the objects, just the sums, they are only there to show how I think, but a drill down could be nice if someone wants to take a deeper look at it).

Cheers!

	Periode	Object	Value
	2023	22	90
	2023	23	85
	2023	24	70
	2023	25	65
	2023	26	40
	2022	22	95
	2022	23	92
	2022	24	75
	2022	25	70
	2022	26	45
	2021	22	97
	2021	23	92
	2021	24	74
	2021	27	78
	2020	22	94
	2020	23	90
	2020	25	75
	How many are represented in all periods	2
	Object/Year	2023	2022	2021	2020
	22	90	95	97	94
	23	85	92	92	90
	Sum	175	187	189	184
	Change	-6,42 %	-1,06 %	2,72 %
	How many are repsented in the 3 last periods
	3
	Object/Year	2023	2022	2021
	22	90	95	97
	23	85	90	92
	24	70	75	74
	Sum	245	260	263
	Change	-5,77 %	-1,14 %
	Object/Year	2023	2022	2021	2020
	22	Down	Down	UP	No change
	23	Down	No change	UP	No change
	No change	0	1	0	2
	Up	0	0	2	0
	Down	2	1	0	0
	How many are repsented in the 3 last periods
	3
	Object/Year	2023	2022	2021
	22	Down	Down	No change
	23	Down	No change	No change
	24	Down	Up	No change
	No change	0	1	3
	Up	0	1	0
	Down	3	1	0

[v-rzhou-msft · Accepted solution]

Hi @nor303 ,

I think you can try code as below to use virtual table in your measure to achieve your goal.

Measure = 
VAR _Generate =
    ADDCOLUMNS (
        GENERATE ( ALLSELECTED('DimObject & Calculation'), ALLSELECTED(DimPerioder[Periode])),
        "Result",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Object] = EARLIER ( [Object] )
                        && 'Table'[Periode] = EARLIER ( [Periode] )
                )
            ) + 0
    )
VAR _Step1 =
    ADDCOLUMNS (
        _Generate,
        "Product", PRODUCTX ( FILTER ( _Generate, [Object] = EARLIER ( [Object] ) ), [Result] )
    )
VAR _Step2 =
    FILTER ( _Step1, [Product] <> 0 || [Object] IN { "Sum", "Change" } )
VAR _Step3 =
    ADDCOLUMNS (
        _Step2,
        "Final",
            SWITCH (
                [Object],
                "Sum", SUMX ( FILTER ( _Step2, [Periode] = EARLIER ( [Periode] ) ), [Result] ),
                "Change",
                    VAR _LastPeriod =
                        SUMX ( FILTER ( _Step2, [Periode] = EARLIER ( [Periode] ) - 1 ), [Result] )
                    VAR _Current =
                        SUMX ( FILTER ( _Step2, [Periode] = EARLIER ( [Periode] ) ), [Result] )
                    RETURN
                        DIVIDE ( _Current - _LastPeriod, _LastPeriod ),
                [Result]
            )
    )
RETURN
SUMX(FILTER(_Step3,[Object] = MAX('DimObject & Calculation'[Object]) && [Periode] = MAX(DimPerioder[Periode])),[Final])

Dynamic Format:

IF(MAX('DimObject & Calculation'[Object]) = "Change","0.00%;-0.00%;0.00%","0")

Result is as below.

You can download my sample file to learn more details.

Best Regards,
Rico Zhou

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