A help needed with structuring a table visual

[hidenseek9]

Hello Power BI Community,

I need a help with structuring a table and to do a variance analysis on product performance.

(PL tab specifically)

Sample BI data

I would like the final table in PL tab to look like a table in below excel data.

I would like to be able to drill down in Power BI on column to get the detail of where the performance gap is coming from.

Desired outcome

In the above dataset I have a lot of data, but I would like to focus on 2018 Jan - Mar on a brand called BIO.

I have several measures to calculate volume and Net Sales in CY (current year) and LY (Last year),

but beginning to think measures may not be the right way to approach this.

Perhaps, I need to unpivot the original dataset in query mode to have all values in one column like below screenshot.

If any of above is not clear, please let me know.

Your help is much appreicated.

Many thanks,

H

[v-juanli-msft]

Hi @hidenseek9

Currently, i have unpivot the "volume and Net Sales" columns, the create a measure

Measure = CALCULATE(SUM(Sheet2[Value]),FILTER(Sheet2,[DATE].[MonthNo]>=1&&[DATE].[MonthNo]<=3))

I will update my reply nextday.

Best Regards

Maggie

[hidenseek9]

@v-juanli-msft

Thank you Maggie.

I will wait for your response.

It will be great to do a variance analysis.

Thanks,

H