Forum Discussion

anromanow's avatar
anromanow
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

Hi anromanow,

 

It will be help if you share some sample data.


Regards,
Xiaoxin Sheng

  • anromanow's avatar
    anromanow
    Frequent Visitor
    9 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        anromanow
        Frequent Visitor
        9 years ago

        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.