Forum Discussion
dynamicly changing result
Hi anromanow,
Currently, calculate table cannot be dynamic changed by slicer. Slicer works on visual level, it won't participate the calculation of actual table.For example:
Support: use slicer to filter the visual records, operate on calculation of measure.
Not support: calculate table based on chosen of slicer.If you want to operate the current table, I'd like to suggest your take a look at below articles which about parameterized query(power query formula):
Using dynamic parameter values in Power Query Queries
Deep Dive into Query Parameters and Power BI Templates
Regards,
Xiaoxin Sheng
Thanks for Your anwser, Anonymous. Actually all my data is on russian, so i prepared a small sampe part of it on english, and it is a bit simplified.
cta_id means counteragent id.
region levels and segment must be a slicers. And to get share I have to sum all values (over cta_id) in column "vehicles boubght in my company" and devide it by sum of all values (over cta_id) in column "vehicles bought total".
It is rather obvious (windowed functions) how to get what I need by SQL, but every time I'll have to recalculate a lot of stuff.
PS. Due to language barier I'm not attaching dependences. This part is not diffcult and I can deal with it.
Hi anromanow,
>>region levels and segment must be a slicers.
Slicer also can affect to calculate table, you can created the relationship between new table and the original table.
>> And to get share I have to sum all values (over cta_id) in column "vehicles boubght in my company" and devide it by sum of all values (over cta_id) in column "vehicles bought total".
Sample formula: summary total amount by cta_id and region.
Table = SUMMARIZE(Sheet1,Sheet1[cta_id],Sheet1[region_level1],Sheet1[region_level2],[region_level3],[region_level4],"Total Bought in Company",SUM(Sheet1[vehicles bought in my company]),"Total bought",SUM(Sheet1[vehicles bought total]))
Regards,
Xiaoxin Sheng
Dear Anonymous
solution You gave is really obvious. I did almost the same a long time ago, when I started to learn power bi,
Actually this ^^ is not working correctly, I'll explain.
At some moment of time I want to see share of vehicle from my company in all vehicles, that counteragent =1 has ever bought.
In such case, yeah, Your solution works, and works well. And I see 30%
But couple moment later I decide to use slicer. Now I want to ignore everything except sport cars. I want to see 50%
And what about trucks? (1+1)/(1+4) I want to see 40%
Unfortunatelly in second (about sport cars) and in thid (about truck) cases I see the same 30%
table, that is calculated using summarize, is connected to source table as *:1 in both directions.
Hi anromanow,
Currently, calculate table cannot be dynamic changed by slicer. Slicer works on visual level, it won't participate the calculation of actual table.For example:
Support: use slicer to filter the visual records, operate on calculation of measure.
Not support: calculate table based on chosen of slicer.If you want to operate the current table, I'd like to suggest your take a look at below articles which about parameterized query(power query formula):
Using dynamic parameter values in Power Query Queries
Deep Dive into Query Parameters and Power BI Templates
Regards,
Xiaoxin Sheng
Anonymous
yeah, this is exsactly waht I was talking about.
the only problem is time. query works about 10 min.