dynamicly changing result

[anromanow]

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

[v-shex-msft]

Hi @anromanow,

It will be help if you share some sample data.

Regards,
Xiaoxin Sheng

[anromanow]

Thanks for Your anwser, @v-shex-msft. 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.

[v-shex-msft]

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

[anromanow]

Dear @v-shex-msft

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.

[v-shex-msft · Accepted solution]

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

[anromanow]

@v-shex-msft 

yeah, this is exsactly waht I was talking about. 

the only problem is time. query works about 10 min.