Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have created a Matrix Visualization that looks like the image below....
The Data Source is a table that has three fields that I use for grouping and summarization - TenantTag, tik_num, and stock_num. I have linked these fields to Rows in the Matrix Visualization. The remaining fields in this table hold the values that I summarize - ActFootage, EstFootage, est_footage_qty_adjust, and total_footage_scanned. I have linked these fields to Values in the Matrix Visualization.
Generally, the value in total_footage_scanned should be very close (within +/- 10%) to the value in est_footage_qty_adjust. My hope for this Visualization is to make it easy to identify cases where this does not hold true.
There will be cases where at the stock_num level, this +/- 10% comparison might indicate a potential problem, but looking at the tik_num level would make it obvious that this discrepancy is easily explainable. (Within one tik_num, we might find that one stock_num was scanned 25% more than expected, but this could be offset by another stock_num that was scanned 25% less than expected, resulting in an even match at the tik_num level.)
With this being possible, I'd like to find a way that would allow me to filter this matrix with the following two user-selectable options:
I am fairly new to Power BI. If anyone here can recommend a general approach I can take to solve a challenge like this (or point me towards an example), I would greatly appreciate it.
Thanks in advance,
Paul
Solved! Go to Solution.
Hi @PaulKraemer
You'll need to compute the percentage difference at both the stock_num and tik_num level.
Difference - stock_num =
VAR _scanned =
SUM ( ScannedVsExpected[total_footage_scanned] )
VAR _qty =
SUM ( ScannedVsExpected[est_footage_qty_adjust] )
VAR _diff = _scanned - _qty
RETURN
DIVIDE ( _diff, _qty )
Difference - tik_num =
VAR _scanned =
CALCULATE (
SUM ( ScannedVsExpected[total_footage_scanned] ),
ALLEXCEPT ( ScannedVsExpected, ScannedVsExpected[tik_num] )
)
VAR _qty =
CALCULATE (
SUM ( ScannedVsExpected[est_footage_qty_adjust] ),
ALLEXCEPT ( ScannedVsExpected, ScannedVsExpected[tik_num] )
)
VAR _diff = _scanned - _qty
RETURN
DIVIDE ( _diff, _qty )
Then write measures as visual filter based on the result of the above measures
Filter - stock_num =
IF ( ABS ( [Difference - stock_num] ) <= 0.1, 1, 0 )
Filter - tik_num =
IF ( ABS ( [Difference - tik_num] ) <= 0.1, 1, 0 )
Create a placeholder table/disconnected table to be able to switch between these two filter measures
Final Filter =
VAR _comparison =
SELECTEDVALUE ( ComparisonOption[Sort] )
VAR _both = [Filter - tik_num] = 1
|| [Filter - stock_num] = 1
RETURN
SWITCH (
TRUE (),
_comparison = 1, [Filter - stock_num],
_comparison = 2, [Filter - tik_num],
_both, 1
)
The percentages and the filter measuers don't need to be in the visual. They're there to easily visualize the results.
Pelase see the attached sample pbix.
Thank you @danextian ! That works great !!
To take this one step further, rather than hard-coding the filter for +/- 10%, is there any way that I could use a user adjustable variable for this percentage? For example, can I change the following measure ...
Filter - tik_num = IF ( ABS ( [Difference - tik_num] ) <= 0.1, 1, 0 )
...... to something like ...
Filter - tik_num = IF ( ABS ( [Difference - tik_num] ) <= MyVariable, 1, 0 )
... and then provide an entry field that allows the user to change the value in MyVariable?
I really appreciate your help!
Thanks again and best regards,
Paul
Hi @PaulKraemer
You will need to write at least two measure - one that returns the % difference by stock_num_level and the other one by tik_num level and use a disconnected table for a possibly a field parameter to switch betwen the two. But as always, please provide a workable sample data (not an image) and your expected result from the same sample data. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
Hi @danextian -
Thank your for your reply. I exported some of the underlying data to an Excel file and recreated my Matrix Visualization in a new .pbix file for the purpose of example. If I did it correctly, here is a link to it on my OneDrive...
.. In my Matrix Visualization, I will use TenantTag = Luminer, tik_num = 5412 as an example for what I am hoping to accomplish.
stock_num | Sum of est_footage_qty_adjust | Sum of total_footage_scanned |
P1139 | 1,129 | 2,129 |
P2065 | 1,129 | 129 |
Total | 2,258 | 2,258 |
In this example, the Sum of total_footage_scanned is 88% higher than the sum of est_footage_qty_adjust for stock_num P1139 AND 88% lower for P2065, but the Totals are the same. In cases like this, if I filtered for percentage difference outside of the +/- 10% at the tik_num grouping level, this tik_num should not be flagged as a "problem". If I filtered for percentage difference outside of the +/- 10% at the stock_num grouping level, both P1139 and P2065 should be flagged as a "problem".
I appreciate your help.
Thanks again!
Paul
Hi @PaulKraemer
You'll need to compute the percentage difference at both the stock_num and tik_num level.
Difference - stock_num =
VAR _scanned =
SUM ( ScannedVsExpected[total_footage_scanned] )
VAR _qty =
SUM ( ScannedVsExpected[est_footage_qty_adjust] )
VAR _diff = _scanned - _qty
RETURN
DIVIDE ( _diff, _qty )
Difference - tik_num =
VAR _scanned =
CALCULATE (
SUM ( ScannedVsExpected[total_footage_scanned] ),
ALLEXCEPT ( ScannedVsExpected, ScannedVsExpected[tik_num] )
)
VAR _qty =
CALCULATE (
SUM ( ScannedVsExpected[est_footage_qty_adjust] ),
ALLEXCEPT ( ScannedVsExpected, ScannedVsExpected[tik_num] )
)
VAR _diff = _scanned - _qty
RETURN
DIVIDE ( _diff, _qty )
Then write measures as visual filter based on the result of the above measures
Filter - stock_num =
IF ( ABS ( [Difference - stock_num] ) <= 0.1, 1, 0 )
Filter - tik_num =
IF ( ABS ( [Difference - tik_num] ) <= 0.1, 1, 0 )
Create a placeholder table/disconnected table to be able to switch between these two filter measures
Final Filter =
VAR _comparison =
SELECTEDVALUE ( ComparisonOption[Sort] )
VAR _both = [Filter - tik_num] = 1
|| [Filter - stock_num] = 1
RETURN
SWITCH (
TRUE (),
_comparison = 1, [Filter - stock_num],
_comparison = 2, [Filter - tik_num],
_both, 1
)
The percentages and the filter measuers don't need to be in the visual. They're there to easily visualize the results.
Pelase see the attached sample pbix.
User | Count |
---|---|
73 | |
69 | |
35 | |
27 | |
26 |
User | Count |
---|---|
96 | |
92 | |
54 | |
45 | |
41 |