The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi folks,
It might a simple task, but I've tried to create the Aux_Column using several different combinations of Calculate, SumX, Divide, Filter, FirstNonBlank, among others but no lucky.
My goal is to have for each IDs in the 'Cycle' column the result of 'Running' / 'Waiting' rows of the 'Driver' column using values from the 'Actual' column. For instance, for Cycle = A, all rows should be 4 (120/30), while for Cycle = B they should be 11.67 (140/12), etc. The issue is that the Cycle column is present only in the raw data, not in the summary table in the visual, therefore when aggregating values for the Aux_Column, most formulas that I've tried are performing 120 + 140 and then dividing by 30 + 12.
Thanks in advance for any help.
Driver | Actual | Cycle | Aux_Column |
Running | 120 | A | 4 |
Jogging | 50 | A | 4 |
Waiting | 30 | A | 4 |
Running | 140 | B | 11.67 |
Jogging | 25 | B | 11.67 |
Waiting | 12 | B | 11.67 |
Solved! Go to Solution.
hi @jpaguiar
try like:
@jpaguiar
Yes you are right. Seems like the context transition effect of the SUMMARIZE function is creating a filter context issue that is not easy to understand. Replacing SUMMARIZE with GROUPBY solves the problem
Hi there @FreemanZ @tamerj1!
You guys have helped me a lot to progress with the data transformation steps that I am having to do in order to create an interactive dashboard, however I got into a new roadblock.
If you have the chance, could you please take at this new challenge?
Driver | Actual | Target | Cycle |
Running | 120 | 100 | A |
Jogging | 50 | 40 | A |
Waiting | 30 | 10 | A |
Running | 140 | 110 | B |
Jogging | 25 | 30 | B |
Waiting | 12 | 20 | B |
I was able to use the proposed solutions to summarize the original table to this aggragated one on which I don't need to specificy the metrics for each Cycle.
Driver | Actual | Target | Delta | R_Actual | R_Target | Diff Target | Factor | R_Delta |
Running | 130.0 | 105.0 | 25.0 | 112.0 | 140.0 | 35.0 | 0.333333 | 8.33 |
Jogging | 37.5 | 35.0 | 2.5 | 41.0 | 40.0 | 0.83 | ||
Waiting | 21.0 | 15.0 | 6.0 | 27.0 | 25.0 | 2.00 |
From the original Actual and Target values, I re-calculated them after some data transformation to get R_Actual and R_Target. Then I calculated a measure that is the difference between R_Target and Target but only for Driver = "Running". Then I created another measure (Factor) so that I could use this factor for re-calculating the data column proportionaly (R_Delta). My issue is that I am unable to pass the factor 0.33 resulting from the measure Factor to all rows to then multiply it by the Target column values for all rows to get 0.83 and 2.00. PowerBI does the multiplication row by row, therefore it shows empty cells for R_Delta for Jogging and Waiting Rows.
Thank you so much in advance!
Hi @jpaguiar
PLease try
Target Factor 2 =
CALCULATE (
[Target Factor],
'Table'[Driver] = "Running"
)
Hi @jpaguiar
Please refer to attached sample file with the proposed solution
Measure =
VAR T1 = CALCULATETABLE ( 'Table', ALLSELECTED ( 'Table' ), VALUES ( 'Table'[Cycle] ) )
VAR T2 = SUMMARIZE ( T1, 'Table'[Driver], "@SUM", SUM ( 'Table'[Actual] ) )
RETURN
PRODUCTX (
T2,
SWITCH (
'Table'[Driver],
"Running", [@SUM],
"Waiting", 1/[@SUM]
)
)
Hi @tamerj1,
It worked great with no filters applied, but then when I filter either cycle A or cycle B, it apparently tries to calculate the division for each 'Driver' individualy, resulting in 'Infitiny'. If I try to use the same strategy but using a calculated column, it again works for unfiltered views but returns 1.04 for either Cycle = A or Cycle = B filters, which I believe is just calculing the sum prior to the division again.
FreemanZ worked fine for both filtered and unfiltered.
Thank you for the support mate!
@jpaguiar
Yes you are right. Seems like the context transition effect of the SUMMARIZE function is creating a filter context issue that is not easy to understand. Replacing SUMMARIZE with GROUPBY solves the problem
hi @jpaguiar
try like:
Hi @FreemanZ,
It worked perfectly for both filtered and unfiltered views!
Superb, man! Thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
33 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
28 |