Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.Name | UltimateDunsNumber | Fiscal year | Account | AccountChannel | AccountSegment | AccountSubSegment | AccountSector | AccountSubSector | HQArea | HQRegion | HQCountry | DeliveryArea | DeliveryRegion | GCSP | DeliveryCountry | ServiceLine | Competency | SubServiceLine | TER_PFYTD_USD | TER_FYTD_USD | TER_FYTD_Constant | RevPlanFY | NER_FYTD_Constant | MarginFYTD_Constant | MarginPFYTD_Constant | TER_PFYTD_Constant | PipelineOpen_constant | SaleFYTD_constant | PipelineWeightedOpen_constant | Region | Account Segment | Custom |
Solved! Go to Solution.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
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
which is not possible with my dataset or might be i need to write some measure to get that
Hi @vjnvinod,
Can you provide some sample data with expected output to make your require more clear?
here is the sample data and requirement is already there in the thread
UltimateDunsNumberAccountAccountChannelAccountSegmentAccountSubSegmentAccountSectorAccountSubSectorHQAreaHQRegionHQCountryDeliveryAreaDeliveryRegionDeliveryCountryGCSPServiceLineSubServiceLineCompetencyTER_FYTD_USD
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | Ind | T | Ind | Ind | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | ITT | T | ITT | ITT | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | Int | T | Int | Int | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | GCR | T | GCR | GCR | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | ITT | C | ITT | ITT | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | ASEAN | Singapore Geo | ITT | T | ITT | ITT | 729.71087 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Greater China | Hong Kong-China Geo | GCR | T | GCR | GCR | 3125.724547 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | PI | A | PI | PI | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | FAA | A | FAA | FAA | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | PAS | C | PAS | PAS | 1608.489188 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | BTS | T | BTS | BTS | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | New Zealand Geo | BTS | T | BTS | BTS | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | ASEAN | Singapore Geo | Aud | A | Aud | Aud | 3810.075412 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | ASEAN | Singapore Geo | ITT | C | ITT | ITT | -729.71087 |
Hi @vjnvinod ,
I used the sample data provided by @MFelix ,and see below:
1. Create 2 table containing the distinct category in column [Delivery Region] and [HQ Region],using below dax expression--->{The 2 tables are created to be sliced}
Table 2 = DISTINCT(UNION(DISTINCT('Table'[Delivery Region]),DISTINCT('Table'[HQ Region])))
Table 3 = 'Table 2'
3.Create 2 measures as below:
Measure =
var a= CALCULATE(MAX('Table'[TER_FYTD_US]),FILTER('Table','Table'[Delivery Region]=SELECTEDVALUE('Table 2'[Delivery Region])))
var b=CALCULATE(MAX('Table'[TER_FYTD_US]),FILTER('Table','Table'[Delivery Region]<>SELECTEDVALUE('Table 2'[Delivery Region])&&'Table'[HQ Region]=SELECTEDVALUE('Table 3'[Delivery Region])))
Return
SUMX('Table',a+b)
Measure 2 = var _table=SUMMARIZE('Table','Table'[Delivery Region],'Table'[HQ Region],"_value",[Measure]) return
SUMX(_table,[_value])
Finally you will see:
For the related .pbix file,pls click here.
Looks like its closer.
can you please replicate this into the last pbix file which Felix has sent.
just wanted to cross check if the numbers are correct
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
not sure if this helps.
but seems like closer, can you share your pbix? so that i can check the numbers?
this is how my sample dataset looks
UltimateDunsNumberAccountAccountChannelAccountSegmentAccountSubSegmentAccountSectorAccountSubSectorHQAreaHQRegionHQCountryDeliveryAreaDeliveryRegionDeliveryCountryGCSPServiceLineSubServiceLineCompetencyTER_FYTD_USD
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | Ind | T | Ind | Ind | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | ITT | T | ITT | ITT | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | Int | T | Int | Int | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | GCR | T | GCR | GCR | 0 |
856350314 | ABC | 2 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Americas | LATAM South | Argentina Geo | Americas | Canada | Canada Geo | ITT | C | ITT | ITT | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | ASEAN | Singapore Geo | ITT | T | ITT | ITT | 729.71087 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Greater China | Hong Kong-China Geo | GCR | T | GCR | GCR | 3125.724547 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | PI | A | PI | PI | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | FAA | A | FAA | FAA | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | PAS | C | PAS | PAS | 1608.489188 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | Australia Geo | BTS | T | BTS | BTS | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | Oceania | New Zealand Geo | BTS | T | BTS | BTS | 0 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | ASEAN | Singapore Geo | Aud | A | Aud | Aud | 3810.075412 |
758143812 | BCD | 1 | C | C | Real Estate, Hospitality & Construction | Construction & Engineering | Asia-Pacific | Oceania | Australia Geo | Asia-Pacific | ASEAN | Singapore Geo | ITT | C | ITT | ITT | -729.71087 |
Hi @vjnvinod ,
Please checlk the PBIX file attach as refered I createda table with unique values for HQRegion and DElivery region then added a measure with the calculation.
I have place also 2 table visualizations from the basic table with Delivery and HQ region calculation to check the values and everything is correct. Please be aware that the name of the columns may have some errors since on our post the names were all together.
Any question please tell me.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
thanks for this, i evaluted this with my bigger dataset and numbers are not what i am expecting.
your measure is basically adding HQ and Delivery revenue instead of HQ and Inbound Revenue
for instance my take a look at US-East, my revenue should have been =456+5=461M
your measure is resulting into 774M(ie because you are taking the whole delivery region)
i have indicated, as green arrow, how the Inbound revenue will have to be calculate(filter for Deliver region- US-East and also filter for all HQ region except US-East
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
this is awesome,
is there anyway this could be included within the data itself?
instead of seprate table for region?
because right now i have only Region and total revenue, i need all the other coloumns from the table 1, so that i can create a dashboard with various other fields as well
Hi @vjnvinod ,
The separate table is only for slicer or categorization of the Revenue value purposes not anything else, you can then calculate based on any field using the same logic.
If you use the slicers from the same table you will not be abble to select the values in a slicer in an independent way from the table because you are creating subsets or using the full table.
So you shoud be abble to create the report normally except if you are using this revenue measure all other stays the same.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshow would i even know, which accounts are contributing to the revenue we measured in the "Table2?
and also if its possible
in that case the only way i can use the table is to have multiple queries built and post that appending each queries to get a master database?
like i was trying to do that in the first thread?
Hi @vjnvinod ,
Measure are based on context meaning that the calculation is based on the values you place in filters, slicers, tables, visualizations and so on.
When you are calculating the measure of the revenue if you add more columns like account the calculations will be remade in order to achieve the desired output, so no need to make new queries just for the account level.
As I refered the table created for the slicer is only to give context if you look at the coding I'm comparing the value in the slicer with the values in the HQ and Delivery region so since HQ and Delivery region are related with the accounts, if you place the accounts on a visualization the result will appear correctly.
If I'm not explaining my self clearly can you provide an example with data and expected output so I can see what is working or not?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
thanks Felix for clarifying
what i am trying to do is
creating a scatter plot visual and a drill through functionality in the scatter plot which will allow users to see which are the account contributing to the revenue we have calculated using that table 2
see below an example i created when with the first table but i was not able to calculate the Actual revenue(which you have claculated using some DAX)
let me know if this is possible, can you share pbix showing this? many thanks
Hi Felix,
are you able to help me on that? if yes, can you share me the pbix, so that i can take a look at it
or is it too dificult too achieve?
let me know
Hi @vjnvinod ,
Not really sure if this is what you need but try iot out, f not please tell me expected result and how are you building the scatter and the drill down visualization.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
thank you it resolved the issue
not sure why i am not able to accept it as solution, it just throwing up some error, while accepting your post as solution to this thread
HI @vjnvinod ,
I have accepted the answer on your behalf. believe is the one I have marked if not please tell me.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |