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
Did I answer your question? Mark my post as a solution!
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português
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.
Kelly
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Did I answer your question? Mark my post as a solution!
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Did I answer your question? Mark my post as a solution!
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 @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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
