Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have a large table that has, among many other columns, Worker ID, Compa Ratio Range before Increase, and Compa Ratio Range after Increase. We would like to compare number of workers in each range before and after increase, we tried doing a bar or bar and chart graphs but because the ranges are in different columns, we couldn't find an axis or a legend that worked for both.
I am mostly an R user so what I would do is create 2 new tables, one with Worker ID and Compa Ratio Range before Increase, add a column Type = "Before Increase", and a second table with the columns Worker ID and Compa Ratio Range after Increase, and add a new column Type = "After Increase". Finally, I would append the 2 tables and now I can use Type as my legend and display the 2 side by side.
I can't use Append Queries because my 2 tables are DAX tables, is there another formula I could use to append them or a chart that will allow me to display the 2 columns side by side?
Thank you,
Solved! Go to Solution.
Hi @Anonymous ,
We can create a table contain all the range and the order number of ranges
Then we can create two measure to calculate the value.
After Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range after Increase]
)
)
)
Before Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range before Increase]
)
)
)
The solution for order the x-axis is to put the index column into the tooltips.
The range table does not need to has relation with other tables.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Looking at your description believe that this can be made using two measures or a column on the model you already have instead of creating one or two new tables.
Can you please share some sample data and expected result?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Below is what the table looks like and the 2 graphs we had side by side for count of workers in the different ranges before/after increase. You can see that even the scale is not the same, which is another issue. So we're looking for side-by-side bars instead. And the Axis needs to be ordered in ascending order (Below 70, 70-79, 80-89, etc).
I tried creating a measure to count the number of times each range appears (frequency), but even then we haven't been able to find what to use as an axis. We tried creating a new table with the ranges and order we needed them (using RELATED and all that), but we can't create a relationship between the 2 columns in my original table to one column in the new table.
Worker ID | Compa Ratio Range before Increase | Compa Ratio Range after Increase |
A123 | Below 70% | 70-79% |
B256 | 90-99% | 90-99% |
C758 | 80-89% | 90-99% |
D547 | 100-110% | Above 110% |
E789 | 80-89% | 90-99% |
F458 | 70-79% | 80-89% |
G877 | 100-110% | 100-110% |
H558 | 90-90% | 100-110% |
G548 | 80-89% | 90-99% |
L825 | Below 70% | Below 70% |
Thanks!
Hi @Anonymous ,
We can create a table contain all the range and the order number of ranges
Then we can create two measure to calculate the value.
After Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range after Increase]
)
)
)
Before Increase =
CALCULATE (
COUNT ( 'Table'[Worker ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
CONTAINS (
FILTERS ( RangeTable[Range] ),
[Range], [Compa Ratio Range before Increase]
)
)
)
The solution for order the x-axis is to put the index column into the tooltips.
The range table does not need to has relation with other tables.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
The solution given by @v-lid-msft , should work as expected.
@v-lid-msft just one thing there is no need to add the index on the visual if you select the column Range on the table that is created for the Ranges then select the Sort by and choose the column index the Range will be sorted without the need to be placed in the visuals.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
You are right, thank you for your addition information, I have learned a lot from there.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |