Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
2 years ago
Solved

How to show summarized data dynamically

Hi all,

I have a requirement to show summary level data in a dot/scatter chat.

Below is the sample data format I have.

I have represented the data in Power BI as below: Sample file attached pbix

Days for phase 1/phase 2/phase 3 are the days between the two dates shown in median.

Now, I want to get the data in the below format to represent as a plot/scatter chart. This should be dynamic and the values should change based on the slicer selection.

 

Requirement is to show as Scatter chart:

 

 

Can someone please let me know how to achieve this in DAX (not M Query) as this is a sample data from the huge data set that is being used.

 

Thank you.

 

 

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    POSPOS 

    output : 

     

    check solution below :

     

    step 1 : 

    create a config table : 

     

    step 2 : 

    create the following measures : 

    phase 1 = 
SUMX(
    scatter,
    DATEDIFF(scatter[start date] , scatter[step1] , day)
)



phase 2 = 
SUMX(
    scatter,
    DATEDIFF(scatter[step1] , scatter[step2] , day)
)



phase 3 = 
SUMX(
    scatter,
    DATEDIFF(scatter[step2] , scatter[final date] , day)
)





all phases = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(config_tbl[days_for]) = "days for phase 1" ,[phase 1],
    SELECTEDVALUE(config_tbl[days_for]) = "days for phase 2" ,[phase 2],
    SELECTEDVALUE(config_tbl[days_for]) = "days for phase 3" ,[phase 3]
)

     

    \

     

    last step : 

    drag and drop to the visual :

     

     

    output : 

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

7 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    POSPOS 

     

    can you please rephrase what you want to achieve ?  i didnt quite fully understand like what the DAX code should do ? 

     

    best regards,

  • POSPOS's avatar
    POSPOS
    Post Partisan
    2 years ago

    Daniel29195 
    I want to achieve a summary level information as below to represent this information in a scatter chart.

    These values should also change based on the slicer selection made. 

     

    • Daniel29195's avatar
      Daniel29195
      Community Champion
      2 years ago

      POSPOS 

      output : 

       

      check solution below :

       

      step 1 : 

      create a config table : 

       

      step 2 : 

      create the following measures : 

      phase 1 = 
SUMX(
    scatter,
    DATEDIFF(scatter[start date] , scatter[step1] , day)
)



phase 2 = 
SUMX(
    scatter,
    DATEDIFF(scatter[step1] , scatter[step2] , day)
)



phase 3 = 
SUMX(
    scatter,
    DATEDIFF(scatter[step2] , scatter[final date] , day)
)





all phases = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(config_tbl[days_for]) = "days for phase 1" ,[phase 1],
    SELECTEDVALUE(config_tbl[days_for]) = "days for phase 2" ,[phase 2],
    SELECTEDVALUE(config_tbl[days_for]) = "days for phase 3" ,[phase 3]
)

       

      \

       

      last step : 

      drag and drop to the visual :

       

       

      output : 

       

       

       

       

      If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
      It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

       

      • POSPOS's avatar
        POSPOS
        Post Partisan
        2 years ago

        Daniel29195 
        After testing the solution provided, I noticed that the values are not showing up correctly when all the values are selected.
        Expected result when all the values are slected is 
        Days for phase 1 = 1
        Fays for phase 2 = blank
        Days for phase 3 = 37

        But we are only getting only Days for phase 1 = 1. I have attached the pbix file with the solution provided here. 


        Could you please check this and suggest