Forum Discussion

Tio_Chiel's avatar
Tio_Chiel
New Member
3 years ago

Calculation a % from 2 independend queries

Hi All,

 

I have 2 queries

1 Cost query where I have per cost account the cost

1 Sales Revenue where I have the revenues

 

Both queries are linked to a mastertable slicer where I can select the periods

I created a measure in the Cost table where I did % Revenue=Sum [Cost] / Sum [Revenue]

 

As a total this works fine but the moment I create a view on Month level the [Cost] is split by month but the Revenues remain total year.

Can I create a measure function that also links the period of the revenue to the period of the cost ??

Need Help

2 Replies

    • Tio_Chiel's avatar
      Tio_Chiel
      New Member
      3 years ago

      Thank for your suggestion; I created a link in both files linking multiple dimensions; Basically this works but  I would like the cost table to be the one always showing the full figures;

      Example: if in Jan Norway has Cost in a specific category but no Sales in that same category I still want it to be shown; Currently in a many to many relation it looks it leaves the cost out where it can not establish the link below a visualization of what I mean Total % should be 8.83 and not 8.67