topic Re: average of tables with no relationships in DAX Commands and Tips

average of tables with no relationships

rkubis — Mon, 31 Jan 2022 17:17:56 GMT

Hi ,

I have a normal fact tables - plan and sales with usage dates and sale dates.I created additional column which is calculating usage_date-sale_date and this integer is than connected to separate table.

I have created running total of sales , total plan for each usage_date than divide those 2 values. So far so good. But what I am struggeling is to create averate of those values for for each (usage_date-sale_date) since those 2 tables are not connected.

I thought to create separate summarize tables than union them than create another summarize of new union table but its not possible. I know I have to use summarize and at the end averagex of that table but I dont know how

I have attached sample data and sample pbix.

excel

pbix

Re: average of tables with no relationships

rkubis — Tue, 01 Feb 2022 12:36:15 GMT

Are you guys able to download the files ?

thanks

Re: average of tables with no relationships

v-yanjiang-msft — Thu, 03 Feb 2022 07:03:44 GMT

Hi rkubis ,

I download your sample, the sale date and usage date columns in sales table and plan table are the same, so the two tables can be merged into one.

Here's my solution.

1. In Power Query, click Merge Queries tab under Home ribbon.

2. Select usage date and sale date columns of the two tables at the same time, then click OK.

3.Get the following result, then click "Table" tab.

Only select sales column.

4.Get the following merged table.

5. Create a measure.

sales RT vs total plan = VAR _salesRT = CALCULATE ( SUM ( 'plan'[sales] ), FILTER ( ALLSELECTED ( 'plan' ), ISONORAFTER ( 'plan'[sale date], MAX ( 'plan'[sale date] ), DESC ) ) ) VAR _TotalPlan = CALCULATE ( SUM ( 'plan'[plan] ), ALLSELECTED ( 'plan' ) ) RETURN DIVIDE ( _salesRT, _TotalPlan )

6.Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

Re: average of tables with no relationships

rkubis — Mon, 07 Feb 2022 16:12:03 GMT

Hi v-yanjiang-msft , I am looking for DAX solution since the Ski_date-sale_date table is created on the fly using DAX.

I have much more tables joining via this table so using powerquery is not really a solution.

anyway thank you

thank you