Forum Discussion

Divous's avatar
Divous
Icon for Helper III rankHelper III
6 years ago
Solved

Count rows based on value and compare them

Hi community,

 

could I kindly ask you for help with this (I think for most of you) easy thing.

 

I have x tables named Month 1, Month 2, and so on with this values for example:

 

I need to calculate how many times a company occurs in a given month and compare it with the occurrence in another month and display the difference (+ -) in the graph.

The bonus would be to show in which city the highest increase occurred (and in which month) and vice versa.

 

Thank you in advance for pushing me for a solution

 

Divous

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Divous ,

     

    You can do like this.

    1. Add a [Month] column in each table, such as:

     

    Month = 1

     

    2. Use the 'Append Queries' feature to merge multiple tables.

    3. Create visuals and calculate the difference.

     

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

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

    Divous , you need add a month name/year/date and then merge this data

    https://radacad.com/append-vs-merge-in-power-bi-and-power-query

     

    add month with help from sheet name

    https://www.mrexcel.com/board/threads/add-custom-column-with-sheet-name-as-value-content-power-query.1093558/

     

    the compare month vs month

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

     

    Power BI — MTD
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

    • Divous's avatar
      Divous
      Icon for Helper III rankHelper III
      6 years ago

      Dear amitchandak 

       

      thanks for your reply! It looks very sophisticated.

       

      I made a date table - very useful, thanks. Just a question what meaning is "Month rank"? because I have in every row number 1.

       

      Thing which stopped me is that I am try to figure out how to sum text string, because Company name is text. Do you have some advice, please?

       

      When I create formulas for compare it shows errors: "The SUM function cannot work with String values." or "The expression specified in the query is not a valid table expression."

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Divous ,

     

    Can you share a screen shot of your power Query Screen.

     

    Wanted to understand how you Tables look like.

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Divous ,

     

    You can do like this.

    1. Add a [Month] column in each table, such as:

     

    Month = 1

     

    2. Use the 'Append Queries' feature to merge multiple tables.

    3. Create visuals and calculate the difference.

     

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.