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

Question

Hi,&nbsp;&nbsp;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).&nbsp;Cheers!&nbsp;&nbsp;PeriodeObjectValue&nbsp;&nbsp;&nbsp;&nbsp;20232290&nbsp;&nbsp;&nbsp;&nbsp;20232385&nbsp;&nbsp;&nbsp;&nbsp;20232470&nbsp;&nbsp;&nbsp;&nbsp;20232565&nbsp;&nbsp;&nbsp;&nbsp;20232640&nbsp;&nbsp;&nbsp;&nbsp;20222295&nbsp;&nbsp;&nbsp;&nbsp;20222392&nbsp;&nbsp;&nbsp;&nbsp;20222475&nbsp;&nbsp;&nbsp;&nbsp;20222570&nbsp;&nbsp;&nbsp;&nbsp;20222645&nbsp;&nbsp;&nbsp;&nbsp;20212297&nbsp;&nbsp;&nbsp;&nbsp;20212392&nbsp;&nbsp;&nbsp;&nbsp;20212474&nbsp;&nbsp;&nbsp;&nbsp;20212778&nbsp;&nbsp;&nbsp;&nbsp;20202294&nbsp;&nbsp;&nbsp;&nbsp;20202390&nbsp;&nbsp;&nbsp;&nbsp;20202575&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;How many are represented in all periods2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Object/Year2023202220212020&nbsp;&nbsp;2290959794&nbsp;&nbsp;2385929290&nbsp;&nbsp;Sum175187189184&nbsp;&nbsp;Change-6,42 %-1,06 %2,72 %&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;How many are repsented in the 3 last periods&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Object/Year202320222021&nbsp;&nbsp;&nbsp;22909597&nbsp;&nbsp;&nbsp;23859092&nbsp;&nbsp;&nbsp;24707574&nbsp;&nbsp;&nbsp;Sum245260263&nbsp;&nbsp;&nbsp;Change-5,77 %-1,14 %&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Object/Year2023202220212020&nbsp;&nbsp;22DownDownUPNo change&nbsp;23DownNo changeUPNo change&nbsp;No change&nbsp;0102&nbsp;&nbsp;Up0020&nbsp;&nbsp;Down2100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;How many are repsented in the 3 last periods&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Object/Year202320222021&nbsp;&nbsp;&nbsp;22DownDownNo change&nbsp;&nbsp;23DownNo changeNo change&nbsp;&nbsp;24DownUpNo change&nbsp;&nbsp;No change&nbsp;013&nbsp;&nbsp;&nbsp;Up010&nbsp;&nbsp;&nbsp;Down310&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;nor303&nbsp;,
&nbsp;
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 &amp; Calculation'), ALLSELECTED(DimPerioder[Periode])),
        "Result",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Object] = EARLIER ( [Object] )
                        &amp;&amp; 'Table'[Periode] = EARLIER ( [Periode] )
                )
            ) + 0
    )
VAR _Step1 =
    ADDCOLUMNS (
        _Generate,
        "Product", PRODUCTX ( FILTER ( _Generate, [Object] = EARLIER ( [Object] ) ), [Result] )
    )
VAR _Step2 =
    FILTER ( _Step1, [Product] &lt;&gt; 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 &amp; Calculation'[Object]) &amp;&amp; [Periode] = MAX(DimPerioder[Periode])),[Final])
Dynamic Format:
IF(MAX('DimObject &amp; Calculation'[Object]) = "Change","0.00%;-0.00%;0.00%","0")
Result is as below.

You can download my sample file to learn more details.
&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

Forum Discussion

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

1 Reply

Related content

Problem with calcul

Calculate Percentage

Calculating Percentages

percentage calculation

Calculation problem

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