How to make a chart with with flexible sum of sales and conversion rate calculation?

[darkmetrics]

Good afternoon!

I have a dataset with daily sales on a website over period between 2021 and 2022. It looks like:

I want to do 2 things:

1. I want to make a flexible chart that shows page views and sales dynamics for daily/weekly/monthly intervals. Weekly or monthly sales and page views should be calculated simply as a sum of daily observations from my data. The week starts on Monday and ends on Saturday. The frequency of data displayed on the chart should be controlled with a button. So I want to achieve something like:
   
   

2. I also want to show on this flexible chart dynamics of the conversion rate (number of purchases divided by the number of sales). I want the conversion rate to be flexible: that means, if the user selects "Weekly" frequency for the chart, # purchases and sales are summarized over each week and divided one by other. For instance, if sales over 1st week of 2021 are equal to 100, page views are equal to 50, then the conversion rate displayed on the chart should be 50% and so on for other weeks. If user of the dashboard selects "Daily" frequency, conversion rate is calculated simply as daily purchases divided by page views. If user selects the "Monthly" frequency for the chart, the purchases and the page views should sum up over the month and the conversion rate should be calculated in the same manner as for weekly or daily frequency.

How can I do (1) and (2) in the most efficient way with Power BI and DAX?

[Greg_Deckler]

@darkmetrics Well, you will want to use WEEKNUM and MONTH to add columns for those in your table. You could use stacked visuals and bookmarks to swap out the visuals based upon the button pushed.

[darkmetrics]

1. Is it possible to do that without additional columns (using some filters in DAX measure)?

2. What would you say about conversion rate?

[Greg_Deckler · Accepted solution]

@darkmetrics So, yes, you could add the columns inside the DAX measure using ADDCOLUMNS. The conversion rate is a simple DIVIDE. So, for example, you could create a DAX measure like this:

Weekly Measure = 
  VAR __WeekNum = WEEKNUM(MAX('Dates'[Date]))
  VAR __Table =
    ADDCOLUMNS(
      'Table',
      "__WeekNum", WEEKNUM([date])
    )
  VAR __Views = SUMX(FILTER(__Table,[__WeekNum] = __WeekNum),[# page views])
  VAR __Purchases = SUMX(FILTER(__Table,[__WeekNum] = __WeekNum),[# purchases])
RETURN
  DIVIDE(__Purchases, __Views,0)

[darkmetrics]

Thank you, it works!