Matrix with measure based on row sums

[Anonymous]

Hi

I have two sets of values (X and Y) that vary by Month and Year.

I am trying to build a matrix of Columns=Month and Rows=Year, where the values use a measure:

X / (sum of Y for entire year)

Eg for 08 2020, the value would be (X for 08 2020) / (sum of Y for 2020)

Currently I can only manage setting a measure of (X for 08 2020) / (Y for 08 2020).

Would anyone be able to help with this please? Sorry I am new to Power BI but any tips would be appreciated!

Thanks

[amitchandak]

@Anonymous , Use date table, when you select month year or that is on the rows, these options can give you a complete year

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear(dateadd('Date'[Date],1, year)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

thanks for your reply.

I don't have dates in my data, only a month and a year. So at each row/col label month/year in the table I need to have (data value X for that month/year)/(sum of data for that year). So can't sum data up to a date

(Apologies if I have misunderstood the explanation)

[amitchandak · Accepted solution]

@Anonymous , You can try measure like, But prefer a separate year month table

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('YearMo'),'YearMo'[Year]=max('YearMo'[Year])))

[Anonymous]

thank you very much!!