Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anromanow
Frequent Visitor

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

1 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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @anromanow,

 

It will be help if you share some sample data.


Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

123.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

yeah, this is exsactly waht I was talking about. 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.