Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Here is my source data:
I also have two identical tables where I select a version:
Now, I am doing a version to version comparison. I select one Version_1 and one Version_2, then run a bunch of Delta calculations:
As an example Net_Amt_V1 is shown below:
What I need help with is aggregating the Explanation column.
I want to be able to filter on "DROP", "ADD", "CHANGE". I also want to be able to create bar charts based on aggregating "Net Amt Delta" by the different Explanations. Any ideas?
Solved! Go to Solution.
Hi @Anonymous ,
If you want to be able to filter on "DROP", "ADD", "CHANGE".
1. First create a new table as below.
2. Write a measure. With this measure, you can filter a single value or multiple values at the same time.
Explanation =
VAR t =
IF (
[Net_Amt_V2] = 0
&& [Start_Month_V2] = 0
&& [End_Month_V2] = 0,
"Drop",
IF (
[Net_Amt_V1] = 0
&& [Start_Month_V1] = 0
&& [End_Month_V1] = 0,
"ADD",
IF (
[Net_Amt_V2] <> 0
&& [Start_Month_V2] <> 0
&& [End_Month_V2] <> 0
&& [Net_Amt_V1] <> 0
&& [Start_Month_V1] <> 0
&& [End_Month_V1] <> 0,
"CHANGE"
)
)
)
RETURN
SWITCH ( TRUE (), t IN VALUES ( Explanation[Explanation] ), t )
3. Set Explanation is not blank in Filters to Show items when the value is not blank.
4. The result is as below with the Explanation slicer.
If you want to be able to create bar charts based on aggregating "Net Amt Delta" by the different Explanations.
1. Write a measure as below.
Aggregate Net Amt Delta =
CALCULATE (
'Main'[Net Amt Delta],
FILTER (
VALUES ( Main[Product ID] ),
'Main'[Explanation] = SELECTEDVALUE ( Explanation[Explanation] )
)
)
2. The result is as below.
You can check more details from here.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If you want to be able to filter on "DROP", "ADD", "CHANGE".
1. First create a new table as below.
2. Write a measure. With this measure, you can filter a single value or multiple values at the same time.
Explanation =
VAR t =
IF (
[Net_Amt_V2] = 0
&& [Start_Month_V2] = 0
&& [End_Month_V2] = 0,
"Drop",
IF (
[Net_Amt_V1] = 0
&& [Start_Month_V1] = 0
&& [End_Month_V1] = 0,
"ADD",
IF (
[Net_Amt_V2] <> 0
&& [Start_Month_V2] <> 0
&& [End_Month_V2] <> 0
&& [Net_Amt_V1] <> 0
&& [Start_Month_V1] <> 0
&& [End_Month_V1] <> 0,
"CHANGE"
)
)
)
RETURN
SWITCH ( TRUE (), t IN VALUES ( Explanation[Explanation] ), t )
3. Set Explanation is not blank in Filters to Show items when the value is not blank.
4. The result is as below with the Explanation slicer.
If you want to be able to create bar charts based on aggregating "Net Amt Delta" by the different Explanations.
1. Write a measure as below.
Aggregate Net Amt Delta =
CALCULATE (
'Main'[Net Amt Delta],
FILTER (
VALUES ( Main[Product ID] ),
'Main'[Explanation] = SELECTEDVALUE ( Explanation[Explanation] )
)
)
2. The result is as below.
You can check more details from here.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , You need to version table or at least one disconnected table. It will work the same way I have done it for dates in this blog
Refer:https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |