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 Region REVENUE Close Date Stage F...
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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
So what is the logic to arrive at the Target Table?
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.