Forum Discussion
dynamicly changing result
Hi everyone, I'm new at power BI. And I have a question
I had already understood that calculated tables do not respond on slicers.
Here is a case: I have table, generated by sql server and imported to power BI. It is about car market
In this table I have counteragents and all information about him, that he gave us: when and what carh he has bought. and where (region) and what type of car(car, truck,bus and so on) and who sold him this car (official dealer, leasing company, random guy and so on) all this colums are slicers. and by updating database some new values can be added, so I have to work with results of query.
according to this table I have calculated (on sql server) how many cars (with all filters) were bought by counteragents in my company, and in all other distinct places.
here is a question: how CORRECTLY calculate counteragent's share of purchases in my company?
using sql I do it like this
select distinct
iif(seller='my company' ,count(cars bought) over (partition by counteragent, region, segment, ...), 0) inMyCompany,
count(car bought) over (partition by region, segment,...) everywhere,
counteragent
from
table1
where
AND HERE I would put slicers I need
And in this case counteragent's share of purchases in my company will be inMyCompany/everywhere
how to perform this? what and how shoud I create in power BI to see how this share depends on slicers.
PS all slicers are in individual tables, that were created by using Distinct functions, all tables are connected to eachother in correct way.
hope You will understand my problem and give me an idea how to do this
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
6 Replies
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
