Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

SQL data of Running Total By Country (WITHOUT DAX)

Hi,

 

I am totally new to Power BI and DAX. I am comfortable with SQL reporting. My issue is I have imported data from SQL reporting queries to visualise on PowerBI.

One of them is creating data of Running Total column from SQL which I need to plot it on a Line Graph. But the value is getting summarized despite of "DON'T SUMMARIZE". I want to plot the line without using DAX as I have my running total data from the SQL query. 

This is the query
select

location ,

date,

new_cases,

sum(new_cases)over(partition by location order by date) as running_total

from dbo.covid_deaths

where continent is not null

order by 1,2 ASC

 

The ouput works fine but not in the graph (LINE AND CLUSTURED COLUMN CHART). It is getting summarised and creating a straight line.

Please help!

 

These are the data in the graph

 

 

Thank you very much!

(Please let me know any additional information required from my end as I am already new,I may miss out some other information)

 

Need Help
Show and Tell

4 Replies

  • YukiK's avatar
    YukiK
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    I think the date type of running_total should be whole number or decimal. Power BI treating it as text or something? It works on my end:

     

     

     

     

    Please give it a thumbs up if this helps!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi YukiK ,

    Thanks for your reply but unfortunatey, it's still not working.

    (NOTE-- I tried seperately with a dummy data like you did works fine for me but not in my present scenario)

    It is already in whole number(checked with decimal as well).

    You can see a snippet of running total data(as you can see they are adding up fine.)

    As  you can see the data type is in number format only.

     

    Thank you very much.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi YukiK /forum members,

     

    I guess I understood the problem. I was adding values from two different tables without forming any relationship.

    They are not directly related. As you can see , in the image. Let me know what kind of relationship needs to be formed based on date or location?

     

     

     

    It will be a great help !

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HI Anonymous,

      I do not so recommend you to use 'many to many' relationship link table records, they may cause some issues for relationship mapping and records analysis.
      For this scenario, you can refer to the following blog to create a bridge table to link two table records:

      How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

      Regards,

      Xiaoxin Sheng