Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Counting Base on Week on Week result - Day in Raw Data

Hi Senior Users,

 

Can you please help to advise on this.  Sample PBI file is here for download.

In the raw data, there is Clicks by Query, URL by Day

 

I have created metric for Week on Week changed Clicks:

Clicks WoW Change = 
var lastweeknum = 
CALCULATE(
    MAX('Date'[YearWeek]),
    FILTER(
        ALL('Date'),
        [YearWeek]<MAX('Date'[YearWeek])
    )
)
var lastweekamount = 
CALCULATE(
    SUM(Metrics[Clicks]),
    FILTER(
          ALL('Date'),
          [YearWeek]=lastweeknum
    )
)
return CALCULATE(SUM(Metrics[Clicks])-lastweekamount)

 

Then, how to get: how many URLs got more or less Clicks in this week compare to last week clicks?

For example,

For week 04-APR, 2 URLs got less Clicks, 9 URLs got more.

For week 11-APR, 3 URLs got less Clicks, 8 URLs got more.

 

Thank you.

H

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how you want to show the visualization, but I assume you want to display the result on the total row of the matrix visualization.

    Please check the below picture and the attached pbix file.

     

     

    WoW more or less count: =
    IF (
    HASONEVALUE ( Metrics[URL] )
    || [Clicks WoW Change] = BLANK (),
    [Clicks WoW Change],
    VAR newtable =
    ADDCOLUMNS (
    SUMMARIZE ( ALL ( Metrics ), Metrics[URL], 'Date'[YearWeek] ),
    "@countdiff", [Clicks WoW Change]
    )
    RETURN
    "Thisweekmore: "
    & COUNTROWS ( FILTER ( newtable, [@countdiff] > 0 ) ) & " / Thisweekless: "
    & COUNTROWS ( FILTER ( newtable, [@countdiff] < 0 ) )
    )

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Use week rank on year week or week start date

    example

    new columns

     

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

     

    measure example

     

    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak  Thanks for the reply, you are always helpful. I will try this!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how you want to show the visualization, but I assume you want to display the result on the total row of the matrix visualization.

    Please check the below picture and the attached pbix file.

     

     

    WoW more or less count: =
    IF (
    HASONEVALUE ( Metrics[URL] )
    || [Clicks WoW Change] = BLANK (),
    [Clicks WoW Change],
    VAR newtable =
    ADDCOLUMNS (
    SUMMARIZE ( ALL ( Metrics ), Metrics[URL], 'Date'[YearWeek] ),
    "@countdiff", [Clicks WoW Change]
    )
    RETURN
    "Thisweekmore: "
    & COUNTROWS ( FILTER ( newtable, [@countdiff] > 0 ) ) & " / Thisweekless: "
    & COUNTROWS ( FILTER ( newtable, [@countdiff] < 0 ) )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the help Jihwan_Kim , I was thinking just to make another table to list:

       

       

      Then when apply URL, it can breakdown to see which of the URLs got more/less.

      But you've provided very helpful info.

      Thanks again.

      Have a nice day!