Forum Discussion

richrich123's avatar
richrich123
Icon for Helper III rankHelper III
6 years ago

Table vizualization not working from 2 fields

Hi,

 

Im a bit stuck on what is probably something simple and wonder if someone can point me in the correct direction. My reporting is based on Dynamics data. In the quote entity, it holds all the data including date created and order value BUT not won date. If I put it into a table of revenue by created date, it works fine (and is what I am after except by won date)

 

 

 

 

In Dynamics, the closed date (actualend) of the quote is actually held in the quoteclose table which I added and linked it via quote ID:

 

 

If I now do the table taking the closed date (actualend) which is in the quoteclose table and the revenue value (NRR) from the quotes table, it doesnt list by month, just a total the same for every month.

 

 

 

 

Im guessing the problem is with the relationship between quote and quoteclose? I tried just getting the close date (actualend) from the quoteclose table and adding it as a new column in the quotes table so I have close date and NRR in the same table but it doesnt see the table as related, onlt the progress, status and users table:

 

 

Any help / answer would be much appreicated.

 

Cheers

Need Help

8 Replies

  • nahid3152's avatar
    nahid3152
    Icon for Helper II rankHelper II
    6 years ago

    Hi richrich123 
    Did u try using master calender for date, with join with a master calendar date to date?
    or u can share ur dummy file if u have no problem.

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

      richrich123 it is an issue with cross filter direction, whatever your NRR measure is, add following in it

       

      NRR Measure = 
CALCULATE (
<YOUR NRR MEASURE EXPRESSION>,
CROSSFILTER(QuoteCloses[QuoteId], Quotes[QuoteId], Both )
)

      basically use the columns in a CROSSFILTER function that has a relationship between quotecloses and quotes table

       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

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

        Hi parry2k , thanks for your suggestion. Regards this item:

         

        <YOUR NRR MEASURE EXPRESSION>

         

        The NRR value isnt a measure, it is a numeric column in the quotes table called NRR in the quotes table NRR. It is simple the value (£) in the quote.

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

    Hi,

     

    I am still confused about your issue.

    Could you please share your file by OneDrive for Business?

    Remember to remove any sensitive data.

     

    Best Regards,

    Giotto

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

      Hi v-gizhi-msft ,

       

      The problem very simply put is if I add a revenue table showing by month and total value and use the 'created date' and '£ value' which are both held in the same quotes table, it works as expected, each month totalled by £ - perfect:

       

       

       

      The problem is, I dont want to use 'created date', I need to report on 'won date' and that date in Dynamics is held in a second table called quotecloses. (I have both tables in my model and they have a relationship via quoteid). As soon as I swap 'created date' to 'won date', it doesnt display correctly by month, it seems to add everything and repeat it for very month:

       

       

      So from what I can see, it works for created date and £value as they are both in the same table but if I use won date and £value it doesnt work, presumably as the won date and £value arent in the same table. So the relationship is presumably the issue although both tables are linked via quoteid

       

      Hope that makes sense