Table vizualization not working from 2 fields

[richrich123]

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

[v-gizhi-msft]

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]

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

[v-gizhi-msft]

Hi,

Please try to make this relationship direction marked by red circle to 'Both'.

Best Regards,

Giotto

[richrich123]

Hi @v-gizhi-msft , I spotted that and tried it yesterday coincidently and it did the trick, thanks!

[parry2k]

@richrich123 it should be SUM( YourTable[NRR] )

[nahid3152]

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]

@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]

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.