Calculate the total number of views & likes per topic

[Obeybe]

Hi everyone,

I'm new in Power BI and I'm wondering if I can achieve the following.

I want to calculate the total number of views & likes for pages in three different language.
The goal is to count the number of views per topic, we use 3 pages per topic, one page per language.
I have a table with:

Colunm "ViewLifeTime" numbers of views per page

Colunm "Likes" numbers of likes per page

Colunm "UniqueID0" Default language page ID
Colunm "OData__SPTranslationSourceItemId" contain the translated page ID with the default language page ID

Can someone explain how I can calculate the three pages views number in a new colunm.

UniqueId0	OData__SPTranslationSourceItemId	ViewsLifeTime	Title	Language	ViewsRecentUniqueUsers	ViewsLifeTimeUniqueUsers	Likes	Comments	Year	Quarter	Month	Day
{72C5881A-DAC9-425F-B0DC-544B8895D737}	{5A5FC4A1-4361-4B88-A49D-E767620D9383}	21	« Les Netnologues » fête ses 10 ans !	fr	1	14	0	0	2024	Qtr 4	December	19
{E35F151E-3C2E-4C6D-8C65-281F307C2E00}	{5A5FC4A1-4361-4B88-A49D-E767620D9383}	265	De Netweters viert z’n 10de verjaardag!	nl	2	221	18	0	2024	Qtr 4	December	19
{5A5FC4A1-4361-4B88-A49D-E767620D9383}	116	‘De Netweters’ celebrates its 10th anniversary!	en	1	105	2	0	2024	Qtr 4	December	19

[Anonymous]

Hi @Obeybe ,

Sorry for the late reply...

Try this

TotalViews =
VAR _table =
    ADDCOLUMNS (
        'Table',
        "isSameTopic",
            IF (
                [OData__SPTranslationSourceItemId] = "",
                [UniqueId0],
                [OData__SPTranslationSourceItemId]
            )
    )
VAR _isSameTopic =
    IF (
        [OData__SPTranslationSourceItemId] = "",
        [UniqueId0],
        [OData__SPTranslationSourceItemId]
    )
RETURN
    SUMX ( FILTER ( _table, [isSameTopic] = _isSameTopic ), [ViewsLifeTime] )

TotalLikes =
VAR _table =
    ADDCOLUMNS (
        'Table',
        "isSameTopic",
            IF (
                [OData__SPTranslationSourceItemId] = "",
                [UniqueId0],
                [OData__SPTranslationSourceItemId]
            )
    )
VAR _isSameTopic =
    IF (
        [OData__SPTranslationSourceItemId] = "",
        [UniqueId0],
        [OData__SPTranslationSourceItemId]
    )
RETURN
    SUMX ( FILTER ( _table, [isSameTopic] = _isSameTopic ), [Likes] )

Best Regards

[Sahir_Maharaj]

Hello @Obeybe,

Can you please try the following:

1. Calculate the total views

Total Views Per Topic = 
VAR CurrentTopic = SELECTEDVALUE('Table'[UniqueID0])
RETURN
CALCULATE(
    SUM('Table'[ViewsLifeTime]),
    FILTER(
        'Table',
        'Table'[UniqueID0] = CurrentTopic || 'Table'[OData__SPTranslationSourceItemId] = CurrentTopic
    )
)

2. Calculate the total likes

Total Likes Per Topic = 
VAR CurrentTopic = SELECTEDVALUE('Table'[UniqueID0])
RETURN
CALCULATE(
    SUM('Table'[Likes]),
    FILTER(
        'Table',
        'Table'[UniqueID0] = CurrentTopic || 'Table'[OData__SPTranslationSourceItemId] = CurrentTopic
    )
)

Hope this helps.

[Obeybe]

Hi Sahir,
Thank you for the solution but because my columns "UniqueID0" & "OData__SPTranslationSourceItemId" are not "Numbers" I receive following error.

Best regards

Obeybe

[Anonymous]

Hi danextian ,thanks for the quick reply, I'll add more.

Hi @Obeybe ,

If I have misunderstood, please show the expected results as a picture.

Creating Calculated Columns with the “SUM” Function.

SUM function (DAX) - DAX | Microsoft Learn

Total views = SUM('Table'[ViewsLifeTime])

Total likes = SUM('Table'[Likes])

Best Regards

[Obeybe]

@Anonymous ,
Thank you for the quick reply.
When I try to use the "SUM" function, this error pops up.

For your understanding what I want to get the total views per topic.
We have three pages per topic, and I want to combine the views in a new column.
Example:

Topic 1, each version is a different page.
8 colleagues show their warm-hearted commitment at our Winter Market (ENG version)
8 collega’s tonen hun warm hart op onze Wintermarkt (Dutch version)

8 collègues témoignent de leur générosité lors de notre Marché d'hiver (French version)

Topic 2
Adjustments to hospitalization insurance as of January 1, 2025 (ENG version)

Aanpassingen hospitalisatieverzekering vanaf 1 januari 2025 (Dutch version)

Adaptation de l'assurance hospitalisation à partir du 1er janvier 2025 (French version)
In the table below you can see that the data in the "UniqueID0" column the same is as the data in the "OData__SPTranslationSourceItemId" column.
I presume that those columns the filter must be to count the amount of views for all the pages per topic.

UniqueId0	OData__SPTranslationSourceItemId	ViewsLifeTime	Sum of TotalViewsLifeTime	Title	Language	ViewsRecentUniqueUsers	ViewsLifeTimeUniqueUsers	Likes	Comments	Year	Quarter	Month	Day
{66ACC9E1-6ECB-4856-A916-7D15B16768E6}		552	228	8 colleagues show their warm-hearted commitment at our Winter Market	en	116	472	7	1	2024	Qtr 4	December	19
{B2319220-31EC-4C4E-B25D-2F8A58F7ADB3}	{66ACC9E1-6ECB-4856-A916-7D15B16768E6}	938	228	8 collega’s tonen hun warm hart op onze Wintermarkt	nl	183	709	17	0	2024	Qtr 4	December	19
{587AB83E-33DF-41F5-A7BB-21A483D8C7CA}	{66ACC9E1-6ECB-4856-A916-7D15B16768E6}	48	228	8 collègues témoignent de leur générosité lors de notre Marché d'hiver	fr	10	35	1	0	2024	Qtr 4	December	19
{0FE8C140-B202-45B7-838D-890FBD7721C4}	{A841E6C7-5720-431B-A9A4-7A1F9B7A4A26}	2	228	Aanpassingen hospitalisatieverzekering vanaf 1 januari 2025	nl	1	1	0	0	2024	Qtr 4	December	19
{DFD580FF-6306-4CDB-9E28-617AD163771E}	{A841E6C7-5720-431B-A9A4-7A1F9B7A4A26}	1	228	Adaptation de l'assurance hospitalisation à partir du 1er janvier 2025	fr	1	1	0	0	2024	Qtr 4	December	19
{A841E6C7-5720-431B-A9A4-7A1F9B7A4A26}		3	228	Adjustments to hospitalization insurance as of January 1, 2025	en	1	1	0	0	2024	Qtr 4	December	19
{BA31D097-46FD-470A-A240-527B0489EC45}	{34EC9B61-E3BD-4C14-856B-FD17C1F59F86}	121	228	BASE lance des pop-up stores en Wallonie : un nouveau concept au service de l’expérience client	fr	4	73	15	3	2024	Qtr 4	December	19
{4F97FF35-1FB9-40C7-8BFD-72C2405C7912}	{34EC9B61-E3BD-4C14-856B-FD17C1F59F86}	343	228	BASE lanceert nieuwe klantervaring met pop-up shops	nl	7	286	13	0	2024	Qtr 4	December	19
{34EC9B61-E3BD-4C14-856B-FD17C1F59F86}		220	228	BASE launches new customer experience with pop-up shops	en	13	189	6	0	2024	Qtr 4	December	19

Best regards
Obeybe

[danextian]

Ensure that the column you are trying to sum is type number. Your screenshot shows it is a string.

[Obeybe]

Hi danextian,
Thank you for the tip.
I cannot change te type of the column "UniqueID0" to number because this column contain also letters.
Exemple: {65670018-2957-4B24-967C-1AB666FBDB69}

[danextian]

Hi @Obeybe 

Can you please post a workable sample data (not an image), your expected result from that and the reasoning behind. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...