i have created below measure to calculate aggregated profit amount for locations at parent level (like Continent, Country, Province) which would be summation of children cities. Our Database stores data at city level only.
Parent Profit Amount := VAR c = SELECTEDVALUE ( DimLocation[LocationKey] ) RETURN CALCULATE ( SUM ( FactAmount[Profit] ), ALL ( DimLocation ), PATHCONTAINS ( DimLocation[Path], c ) )
Now the issue is, how can i handle if there are multiple selected values ? Because here, SELECTEDVALUE () function expects a single distinct value and if its given multiple values it doesn't show anything. Suppose below data exists in Database:
And i just want to show "City" and its "Profit Amount" in chart.
The above measure won't display anything for Location="London" because for "London" location SelectedValue() function gets 3 locationkey values "10,11,12". So in the chart London isn't shown at all.
Apologies since I'm not familiar with the PATHCONTAINS function, but wouldn't this measure work?
Total by city = CALCULATE([Sum of Profit], ALLEXCEPT(table, table[city]))
@amarsale85 If you are having trouble with @lbendlin 's suggestion, which is a good one, see Text to Table: https://community.powerbi.com/t5/Quick-Measures-Gallery/Text-to-Table/m-p/1312929#M594
Thank you lbendlin for your suggestion but it isn't working. Below is my code:
Sum Amount:= VAR SelectedLocations = SELECTCOLUMNS( VALUES(DimLocation), "LocationID", DimLocation[LocationID]+0 ) VAR mytable = SELECTCOLUMNS ( GENERATE ( ALL(DimLocation), ADDCOLUMNS ( GENERATESERIES ( 1, PATHLENGTH ( [Calculated Path] ) ), "PivotPath", PATHITEM ( [Calculated Path], [Value], 1 ) ) ), "LocationID", DimLocation[LocationID]+0, "PivotPathKey", [PivotPath] ) RETURN CALCULATE ( SUM(FactAmount[Amount]), INTERSECT(mytable,SelectedLocations) )
This code gives me error.
I used INTERSECT here, but it gives error since these 2 tables have different no. of columns. If i used NATURALINNERJOIN instead of INTERSECT, that gives an error as well.
Can you suggest what else can be used in place of INTERSECT() ?
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.