Forum Discussion

hidenseek9's avatar
hidenseek9
Post Patron
7 years ago

A help needed with structuring a table visual

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

 

Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      hidenseek9
      Post Patron
      7 years ago

      v-juanli-msft

       

      Thank you Maggie.

      I will wait for your response.

      It will be great to do a variance analysis.

       

      Thanks,

       

      H

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    For the P&L drill-down layout with CY vs LY variance, the cleanest native approach is a Matrix with rows as your product hierarchy and columns as CY/LY measures. For the drill-through on variance column, you'd set up a separate detail page with cross-report drill-through.

    One thing worth knowing: if brand managers need to explore different cuts of the data themselves (swap rows/columns, add DoD/MoM) without coming back to you, Flexa Tables (AppSource) handles this without rebuilding anything variance columns and period comparison are built-in, no DAX needed.