Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Making a visual that pulls data from two tables

Hi, I want to use a visual to show the projected and actual sales of a product by quarter such as in Table C. My data sources would be Table A and B. I am new to powerBI and would be really grateful ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    Try this, use the first three columns in tableB

    Then create this measure

    Actual =
CALCULATE (
    COUNTROWS ( 'Table A' ),
    FILTER (
        ALL ( 'Table A' ),
        'Table A'[Product] = MIN ( 'Table B'[Product] )
            && QUARTER ( 'Table A'[Sales Date] )
                = VALUE ( RIGHT ( MIN ( 'Table B'[Quarter] ), 1 ) )
    )
)

    Result

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , In table one you can create Qtr based on any of the date

 

example

c

 

Then you can create a combine key and join both tables

 

Key = [Product] & "-" [QTR]

 

you can not get data in visual together

 

do in Power Query , new columns

="Q"& Number.ToText(Date.QuarterOfYear([sales Date]))

 

In both tables

"Q"& Number.ToText(Date.QuarterOfYear([sales Date]))

 

 

merge two tables

 


Append and merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-query