How to have 3 measure in a matrix year by year and calculation also

[oupepasa]

Hello,

I need help to construct a matrix on powerbi, so here is what i need to do

Matrix to do 

I did put the calculated value on the row but i don't know how to have a column with last year or how to have last_year/current_year

Here is the result i got for the moment https://i.imgur.com/wdVNkuv.png .

Any advice would be great!

Regards

[v-janeyg-msft]

Hi, @oupepasa 

Is your problem been solved? 

From the data you gave, I can't see how to get the following results. Can you explain it?

Best Regards

Janey Guo

[oupepasa · Accepted solution]

I didn't find any solution so i choose to make 3 table, one for units, one for sales , and one for average price .

[negi007]

@oupepasa  in this case you can create seperate measures for each year like below and then measure for variance

2020A = calculate(sum(vol), year='2020')

2021A = calculate(sum(vol), year='2021')

%var = 2021A/2020A-1

if you face challenge in creating measure, do write back.

[oupepasa]

Thanks for your answer, but what do you mean by (vol), if you mean the quantity, it will not works for the turnover or the average price.

Regards

[negi007]

@oupepasa can you share your sample data in text or excel format.

[oupepasa]

SK_BillingDate QuantityInvoiced SalesPrice
20210912 1.00000 4600.00000
20210912 1.00000 500.00000
20210912 1.00000 1598.00000
20210912 1.00000 5699.00000
20190910 1.00000 23.22000
20190911 1.00000 10750.00000
20190911 1.00000 7200.00000
20200911 1.00000 1575.00000
20200911 1.00000 507.00000
20200911 10.00000 16.30000

In fact all i need for the first 2 column and the %var is that and a date dimension.

i need to calculate for the third line (sales on quantity) but i don't see what i have to put on my measure for my column as all my line are already calculated measure.

[oupepasa]

In fact i don't think, i can do all, because after that i have to add the budget that is not in the same fact table

[negi007]

@oupepasa it does not matter, you can add a seperate budget table in the model. As long as your budgt table has date field, you can have the matrix that you wish to have.

1 this is your data. I have added date column using SK_billingdate

Date = DATE(LEFT('Table'[SK_BillingDate],4),MID('Table'[SK_BillingDate],5,2),RIGHT('Table'[SK_BillingDate],2))

2. then i create date table like below

Calendar = CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date]))

3. Now link your tables like below. same way you can add your budget table and link with date table.

4. create measure to calculate avg billing qty

Avg_Unit_Price = SUM('Table'[SalesPrice])/SUM('Table'[QuantityInvoiced])

5. Below is the visual

[oupepasa]

Thanks a lot for your effort, i didn't explain well because i have a filter for the month and year that i didn't told you about, so how should i put the column 2020 with sameperiodlastyear?

And mostly with a table budget same structure than the sales , how do you add a budget column on this matrix ?i can't see how it would fit with the value from the sales table

[negi007]

@oupepasa I have added a dummy budget table with only selling price. You can have similar table in your dataset as well and link it with the date table.

Your model view would be like below. I have added few measure which are not being used in the final visual. You can keep or remove them

Below is the visual that you wish to have. It is not exactly the same but might do the job for you.

Finally, i am sharing updated pbix file for you. You can make changes and use accordingly in your dataset.

[oupepasa]

Thanks so much for your work, can you confirm me the visual i was looking for isn't possible?

[negi007]

@oupepasa there is nothing impossible. the visual you need would require some more changes in your data structure. 

[oupepasa]

can you give me some advice on the change i would need , because i totally struggle here