Adding Trend Line， Track the Result by Group

[h_l]

Hello everyone,

To track and compare what actions attribute the most sigificant change on data, I'm going to add more trend lines in visual.

Sample PBI file is here.

In dashboard, an overall trend line visual has been created, to summarzie the total impressions and show by date:

What we want, is to add more trend lines to sum impressions specific Campaign ID in specific Action Week or Type. (See following raw table)

E.g. A new line for WK6, another for WK10, etc...

In line "WK6", Sum Impressions where Page (in Raw Table) contains value in Campaign ID in WK6, which are news029164 and news012808

There are 2 tables:

- Progress Table: manually filled by business teams. The "Campaign Page ID" is contained in "Page" column in Raw Table.

Please be noted, one Campaign ID can appears multi times.

- Raw table: exported from DB.

It's acceptable by creating Measure one by one, e.g. create "Campaigns in WK6" for a line, create another Measure "Campaign in WK10" for another line.

Reason:

- there are over 20 Million rows in the real-data. Adding one more column might lead to a heavy load and performance issue.

- the action is not taken every weeks, so I think I can accept to create Measure by using DAX one by one, if the performance is better than adding one more column in Raw Table.

Any idea? Sample PBI file is here.

Thanks & have a safe day.

H

[h_l]

Hi @v-kelly-msft 

While it's working perfectly on Table Tisual, the breadown by ActionType or ActionWeek data cannot be added into trendline:

But once I tried to breakdown by either ActionType, or ActionWeek, I cannot add "Total Impressions" anymore.

Since the purpose is to give a insight on: What Type of Action (or action taken in which week) triggers the biggest improvment, the Idea line visual is to display Total Impressions and Each ActionType (or ActionWeek) Sum Impressions in same Visual for comparison.

Please let me know if there is a workaround.

Thanks again for the great help from you.

H

[v-kelly-msft]

Hi  @h_l ,

You could create a date table as X axis:

Date = UNION(VALUES('Progress Record'[Action Date]),VALUES('Raw'[Date]))

Then create a relationship as below:

Then you can put ActionType, or ActionWeek in the visual.But for your sample data,there's no relationship between dates in 2 tables,so you would see nothing when you put the field into the visual.

Check my updated .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[h_l]

Hi @v-kelly-msft ,

I tried to create relation between 2 tables on Dates, but still nothing.

Seems I didn't get the point, sorry for my baby level understanding in PBI.

But anyway, the 1st reply from you resolve the problem, I will create 2 visuals as workaround.

Visual 1 (left) add trend lines: for Total Impression, and Total Measure 2 (Final Calculation) to see the total change on progress.

Visual 2 & 3: add trend lines: for Total Measure 2 breakdown by "Week" or "Type" as Legend, to see how each Type or Week contribute to Total Changed Progress.

[h_l]

I tried to create Measure like following

Measure = 
VAR inPR =
SELECTEDVALUE('Progress Record'[PageID])
Return
calculate(sum(Raw[Impressions]),
        FILTER ( Raw
        , SEARCH (inPR, Raw[URL], 1, -1 ) > 0 ))

But seems there is something wrong if check on by "Week" Total.

Appreciated if there is any help on this.

[v-kelly-msft · Accepted solution]

Hi  @h_l ,

Create a measure as below:

Measure 2 = SUMX(VALUES('Progress Record'[Campaign Page ID]),'Progress Record'[Measure])

And you will see:

For the related ,pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[h_l]

@v-kelly-msft 

Appreciate it!

This solution can make it either sum by Action Type, or Action Week, able to breakdown by date with hierarchy. Superb!

[parry2k]

@h_l it will be easier if you share a sample pbix file, remove sensitive information before sharing.

[h_l]

Hi @parry2k 

I'm not sure why the uploaded file was deleted.

I just updated the link, please try.

Here is the sample file.

Thanks for reply.

H

[parry2k]

@h_l you can use action week or action type on the legend and it will give you a line for each week or type. You can use only one column on the legend.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[h_l]

Hi @parry2k , i think i need to search the Campaign ID from Page to get sum of impressions first, since they are "contained".

So that I cannot directly build active relations.

On the other hand, it's multi-to-multi since Campaign ID repeats in Progress Table.