Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATA modelling

 
Hi I am new to power bi, please help me with data modeling of the Win and Forecast table with the target table, data cleaning not required
 
  Win Table
RegionREVENUEClose DateStageFiscal PeriodIndiviual
US3159.6303/13/20WinQ1-2020A
US544001/16/20WinQ1-2020A
US11054.5201/16/20WinQ1-2020A
US11383.1101/23/20WinQ1-2020A
US12314.402/23/20WinQ1-2020A
US360003/25/20WinQ1-2020B
US3703.70402/24/20WinQ1-2020B
US5444.44401/27/20WinQ1-2020B
US691204/01/20WinQ2-2020B
US11105.0302/28/20WinQ1-2020B
US15555.5604/01/20WinQ2-2020B
US19555.5604/08/20WinQ2-2020B
US40740.7401/17/20WinQ1-2020B
US67541.4802/10/20WinQ1-2020B
US71563.701/20/20WinQ1-2020B
US81590.3701/22/20WinQ1-2020B
US174392.603/17/20WinQ1-2020B
US350003/19/20WinQ1-2020c
US10113603/31/20WinQ1-2020c            
Forecast Table
RegionREVENUEClose DateStageFiscal PeriodIndiviual
US3159.6303/13/20FORCEASTQ1-2020A
US544001/16/20FORCEASTQ1-2020A
US11054.5201/16/20FORCEASTQ1-2020A
US11383.1101/23/20FORCEASTQ1-2020A
US12314.402/23/20FORCEASTQ1-2020A
US360003/25/20FORCEASTQ1-2020B
US3703.70402/24/20FORCEASTQ1-2020B
US5444.44401/27/20FORCEASTQ1-2020B
US691204/01/20FORCEASTQ2-2020B
US11105.0302/28/20FORCEASTQ1-2020B
US15555.5604/01/20FORCEASTQ2-2020B
US19555.5604/08/20FORCEASTQ2-2020B
US40740.7401/17/20FORCEASTQ1-2020B
US67541.4802/10/20FORCEASTQ1-2020B
US71563.701/20/20FORCEASTQ1-2020B
US81590.3701/22/20FORCEASTQ1-2020B
US174392.603/17/20FORCEASTQ1-2020B
US350003/19/20FORCEASTQ1-2020c
US10113603/31/20FORCEASTQ1-2020c
 
Target Table
Individual TargetQ1-2020Q2-2020Q3-2020Q4-2020
A213,000213,000213,000414,000
B328,000328,000328,000328,000
C 190,000190,000190,000
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    1.

    Go to Transform data, select "Individual Target" column in "Target" table, then "Unpivot other columns", rename columns, colse&&apply.

     

    2. create new tables

    date:

    date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"quarter",FORMAT([Date],"q"))

add columns
year-quarter = "Q"&[quarter]&"-"&[year]

    Individual table

    Individual = VALUES(Win[Indiviual])

    3

    create relationships among tables

    4

    Create measures, add them in a matrix visual

    Win Value = CALCULATE(SUM(Win[REVENUE]),FILTER(ALLSELECTED('date'),'date'[year-quarter]=MAX(Target[year-quarter])))

Forecast value = CALCULATE(SUM(Forecast[REVENUE]),FILTER(ALLSELECTED('date'),'date'[year-quarter]=MAX(Target[year-quarter])))

    Now, you could see the "win value", "forecast value","target value" per year/quarter for each individual.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It might look weird, but this is the kind of data I have. The target table is coming from a different source and the other two tables are from another source... Some reports I need to make based on these data..type. The win numbers required to show against the target.

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

    Hi Anonymous 

    1.

    Go to Transform data, select "Individual Target" column in "Target" table, then "Unpivot other columns", rename columns, colse&&apply.

     

    2. create new tables

    date:

    date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"quarter",FORMAT([Date],"q"))

add columns
year-quarter = "Q"&[quarter]&"-"&[year]

    Individual table

    Individual = VALUES(Win[Indiviual])

    3

    create relationships among tables

    4

    Create measures, add them in a matrix visual

    Win Value = CALCULATE(SUM(Win[REVENUE]),FILTER(ALLSELECTED('date'),'date'[year-quarter]=MAX(Target[year-quarter])))

Forecast value = CALCULATE(SUM(Forecast[REVENUE]),FILTER(ALLSELECTED('date'),'date'[year-quarter]=MAX(Target[year-quarter])))

    Now, you could see the "win value", "forecast value","target value" per year/quarter for each individual.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.