CODEWITHSUNDEEP
sql-serverssispowerbi
Junsh
Junsh

Reputation: 39

Creating Power BI Report with values pulled from SQL Server database parameterized based on user Input

There is a table which has a year worth of data in a database that needs to be pulled and turned into a power bi report. If this was a static report it wouldn't be an issue but this report takes in user input where based on a certain date range and store range certain values needs to change.

Giving a table example which has the data

date store Total interval
2-3-2023 3 50 15
2-5-2023 3 70 15
2-10-2023 5 30 15
2-13-2023 5 20 15
2-18-2023 8 40 15
2-22-2023 8 80 13
2-27-2023 3 10 13

I need to calculate a certain formula which is SUM(Total) [within specific store and interval id] / COUNT(Distinct Date)

If follow this formula the total should be

(140/2) + (50/2) + (40/1) + (80/1) + (10/1) = 225

Now when this is pulled by PowerBI, it needs the date to be there for pulling because the user will filter out date and store accordingly. The problem is that based on the filter the values of the formula needs to change dynamically based on that input and my question is if there is a way to do this?

If you were to put a record by itself per date and add it together the total would be different (300)

Any ideas on how to make the value dynamic in a parameter type of basis via SQL Server, Power BI, or SSIS?

Also note: the user cannot do anything technical and access SQL Servers only front end.

Upvotes: 0

Views: 37

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12061

Try a DAX Measure similar to the following:

MyMeasure = 
  SUMX(
    SUMMARIZE(
      'YourTable',
      [store],
      [interval],
      "SumTotal", SUM([Total]),
      "DateCount", DISTINCTCOUNT([date])
    ),
    DIVIDE( [SumTotal], [DateCount])
  )

Which gives 215 based on your table above.
(Not 225 as 50 + 70 = 120 😉)

Upvotes: 0

Related Questions