Mimic a measure to match column value

[kobkabnaja]

Hi community,

This might be a common "why you ask" question, but I am struggling to understand. I am working on a comparison report between two states, following this video (https://www.youtube.com/watch?v=vzI9T3Wdpak&t=184s).

This video does not explain how the "Total Sales" measure was created in the model. I believe it replicates the "Total Sales" column but as a measure. I tried creating it myself, but it did not work (please see the attachment). I would appreciate suggestions on how to adjust it to match the column values.

Thank you,
K.

[Anonymous · Accepted solution]

Hi @kobkabnaja ,

Thanks for the reply from parry2k .

Here is my example data:

Year	Name	Value
2020	A	100
2020	B	200
2020	C	300
2020	A	400
2020	B	500
2020	C	600
2020	A	700
2020	B	800
2020	C	900
2020	A	1000
2020	B	1100
2020	C	1200
2020	A	1300
2020	B	1400
2020	C	1500
2020	A	1600
2020	B	1700
2020	C	1800
2020	A	1900
2020	B	2000
2021	C	110
2021	A	210
2021	B	310
2021	C	410
2021	A	510
2021	B	610
2021	C	710
2021	H	810
2021	A	910
2021	B	1010
2021	C	1110
2021	A	1210
2021	B	1310
2021	C	1410
2021	A	1510
2021	B	1610
2021	C	1710
2021	A	1810
2021	B	1910
2021	C	2010
2022	A	120
2022	B	220
2022	C	320
2022	A	420
2022	B	520
2022	C	620
2022	A	720
2022	B	820
2022	C	920
2022	A	1020
2022	B	1120
2022	C	1220
2022	A	1320
2022	B	1420
2022	C	1520
2022	A	1620
2022	B	1720
2022	C	1820
2022	A	1920
2022	B	2020
2023	C	130
2023	A	230
2023	B	330
2023	C	430
2023	A	530
2023	B	630
2023	C	730
2023	A	830
2023	B	930
2023	C	1030
2023	A	1130
2023	B	1230
2023	C	1330
2023	A	1430
2023	B	1530
2023	C	1630
2023	A	1730
2023	B	1830
2023	C	1930
2023	A	2030
2024	B	140
2024	C	240
2024	A	340
2024	B	440
2024	C	540
2024	A	640
2024	B	740
2024	C	840
2024	A	940
2024	B	1040
2024	C	1140
2024	A	1240
2024	B	1340
2024	C	1440
2024	A	1540
2024	B	1640
2024	C	1740
2024	A	1840
2024	B	1940
2024	C	2040

Want to calculate sum by grouping by year, name, use the following syntax:

Measure = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Name], 'Table'[Year] )
)

Replace the table names and column names with your own.

The visual effect of the final page is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[parry2k]

@kobkabnaja what is the expected output and how does the raw data looks like?