Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
6 years ago
Solved

Measure for Calculatng Revenue

hi,

 

I have a data with fields like below

I need to calculate revenue for Region

for one region, i was trying to do this in power query(like below)

HQAsean Revenue= Table.SelectRows(#"Renamed Columns3", each ([HQRegion] = "ASEAN"))

InboundAseanRevenue= Table.SelectRows(#"Renamed Columns3", each ([HQRegion] <> "ASEAN") and ([Region] = "ASEAN"))

Append this 2 table to get AseanTotal Revenue

and the outcome is fine.

however the data i have is for 27 regions, so practically the above approach is impossible, as i will have to create (27*3=84 queries)

is there any measure or calculated coloumn i can use to get there?

any help would be highly apprecaite

 

 

Source.NameUltimateDunsNumberFiscal yearAccountAccountChannelAccountSegmentAccountSubSegmentAccountSectorAccountSubSectorHQAreaHQRegionHQCountryDeliveryAreaDeliveryRegionGCSPDeliveryCountryServiceLineCompetencySubServiceLineTER_PFYTD_USDTER_FYTD_USDTER_FYTD_ConstantRevPlanFYNER_FYTD_ConstantMarginFYTD_ConstantMarginPFYTD_ConstantTER_PFYTD_ConstantPipelineOpen_constantSaleFYTD_constantPipelineWeightedOpen_constantRegionAccount SegmentCustom
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    6 years ago

    Hi  vjnvinod ,

     

    You are correct I included every values in the calculation change the var to the following:

     

    var Delivery_Revenue = CALCULATE(SUM('Table'[TER_FYTD_USD]),filter(ALL('Table'[DeliveryRegion],'Table'[HQRegion]),'Table'[DeliveryRegion] = SELECTEDVALUE('Table 2'[HQRegion]) && 'Table'[HQRegion]<> SELECTEDVALUE('Table 2'[HQRegion]))))

    Should give expected result if not please tell me. 

22 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi vjnvinod ,

     

    Why are you make the creation of this calculation on the Query Editor?

     

    If you add the region on a visualization and the the revenue value it will get the total you need. It's not necessary to create a new column on your model.

     

    Also if you want to have this on the query editor you should do a Group By.

     

    Can you share a sample of your data and expected result

    • vjnvinod's avatar
      vjnvinod
      Icon for Impactful Individual rankImpactful Individual
      6 years ago

      MFelix 

       

      Why are you make the creation of this calculation on the Query Editor?

      to answer this

      in my dataset i have

      HQ Region , Delivery Region and TER_FYTD_USD(which is revenue)

      now if you use HQ Region, you aggregate in the visualization you get HQ revenue

      and if you use Deliver region and aggregiate you get Local revenue reported in that region.

       

      Now the logic, what i am trying to build is

      HQ Region revenue + Inbound Revenue= my Total Revenue for that region

      HQ revenue is easy, filter HQregion (Lets say Africa) and you get the africa HQ revenue

      To get Inbound revenue (work delivered in the region on Accounts whose HQ is in other global regions  )of a particular region in my dataset, for example Africa, i need to 

      Unselect Africa from HQ region, and select all other region in HQ Region and select only Africa from Delivery region. 
      which will give me the inbound revenue
      then i will have to sum (HQ Africa revenue+Inbound Africa revenue)

      which is not possible with my dataset or might be i need to write some measure to get that

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi vjnvinod ,

         

        You don't need to have a new table. I assume that both Regions and Delivery have the same type of data correct? something similar to this:

         

        HQ Region Delivery Region TER_FYTD_US
        Africa Europe 1000
        Europe America 500
        America Africa 200
        Africa Africa 500

         

        If you add a new table with the regions without a relationship with other tables you can calculate a measure similar to this one:

        Revenue =

var HQ_Region_Revenue = CALCULATE(Table[TER_FYTD_US]);Table[HQ Region] = SELECTEDVALUE(Table[Region]))

var Delivery_Revenue = CALCULATE(Table[TER_FYTD_US]);Table[Delivery] = SELECTEDVALUE(Table[Region]))

Return
HQ_Region_Revenue + Delivery_Revenue