Calculation between totals in different tables

[Andreasweiz]

Hello,

I am trying to calculate a percentage between two values whichs are in two different matrixes. See screenshot.

For example, I'd like to divide the total for week 31 from the top matrix (lets call the table behind it Table1) by the total for week 31 from the bottom matrix (Table2). To be able to calculate a percentage.

Seems relatively straight forward but I am not able to do it.

You're help would be appreciated.

Thanks,
Andreas

[amitchandak]

@Andreasweiz , If they are two different data tables in power bi(Not only visual) , Then you need a common date/week table and then you can take diff when data is displayed by week

or you can create a week wise measure

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures , create like this for both tables 
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

[Andreasweiz]

Hi,

Thanks for your answer.

I am trying to go down the first route you mentioned as the second I get this:

And I'd like to continue in DirectQuery mode.

I have created a table called YearWeek containing distinct year/week values and have created a relationship between it and the two tables which are involved.

I now need some sort of DAX calculation to get this division between two values from two different tables.

Thanks,

Andreas

[v-yiruan-msft]

Hi @Andreasweiz ，

Could you please provide the setting of the top and bottom matrix in the Fields panel and some sample data from the involved tables? Since the table fields referenced by these two matrixs are from different tables and is there any relationship created between them? Do you want to get the percentage of data for the same week for the top and bottom matrix?

For the percentage calculation, you can refer to the following links to get it by using DIVIDE function.

Calculating Percentages

Calculating Percentage (%) Margins In Power BI Using DAX

Best Regards