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.
Customer | Score | grade | Q1 | Q2 |
AA | 80 | gold | 1 | 1 |
BB | 75 | silver | 1 | 1 |
CC | 80 | gold | 1 | 1 |
BB | 75 | silver | 2 | 2 |
CC | 75 | silver | 2 | 2 |
DD | 80 | gold | 2 | 2 |
CC | 65 | bronze | 3 | 3 |
DD | 75 | silver | 3 | 3 |
EE | 100 | platinum | 3 | 3 |
I have a table as above. my aim is to compare in 2 pie chart that only customers belongs to compare months.
İ.e If i compare q1 and q2
bb and cc need to be compared because aa is not in q2 and dd is not in q1. and grade need to be q1 1 silver 1 gold, q2 2 silver
if compare q1 and q3 only cc need to be copared in pie chart.
I have 2 slicers for quarters in my page and can check changes in table but not in pie chart.
As you can see on power bi pi charts shows 3 customer and comparing them for quarters. however, as you can see on table there are only 2 customer in both quarter and I want to compare them in pie charts. So i need a slicer to see new customers and removed customers and customers that active on both quarter. below you can check the test pbix file.
Solved! Go to Solution.
Hi @selpaqm,
I have a super-complicated solution for your problem with 4 additional auxiliary tables.
If you have a small dataset and won't be frightened by the look of this solution, it will do, otherwise - hope someone proposes a cuter solution.
Here you can download the PBIX file - https://www.dropbox.com/s/p038xljt4ejghy0/community-2-pies-v2.pbix?dl=0
Here's how it works:
1. The initial table is the same but without the Q2 column. It will be added later and will be different from yours.
2. We create 2 additional tables with the help of the following [DAX] code:
Augmented Table 1 =
VAR _tbl = SELECTCOLUMNS ( 'dataset',
"Customer2", [Customer],
"Q2", [Q1] )
VAR _hugetbl = CROSSJOIN ( 'dataset', _tbl )
VAR _filter1 = FILTER ( _hugetbl, [Customer] = [Customer2] )
VAR _filter2 = FILTER ( _filter1, [Q2] <> [Q1] )
RETURN _filter2
You'll get something like this:
Customer2 is not needed here, it is used only for filtering earlier but I don't remove it in order not to make code even more complicated.
3. We create Q1 and Q2 tables which we will use in slicers:
Q1 = VALUES ( 'dataset'[Q1] )
Q2 = SELECTCOLUMNS ( VALUES ( 'dataset'[Q1] ), "Q2", [Q1] )
4. We arrange 4 connections between tables in this auxiliary model:
'Q1'[Q1] -> 'Augmented table 1'[Q1]
'Q1'[Q1] -> 'Augmented table 2'[Q2]
'Q2'[Q2] -> 'Augmented table 1'[Q2]
'Q2'[Q2] -> 'Augmented table 2'[Q1]
5. We use 'Augmented table 1' for the first pie chart and 'Augmented table 2' - for the second.
As you can see, the results are the ones you expect to obtain:
Should you need any clarifications, let me know.
Hi @selpaqm,
I have a super-complicated solution for your problem with 4 additional auxiliary tables.
If you have a small dataset and won't be frightened by the look of this solution, it will do, otherwise - hope someone proposes a cuter solution.
Here you can download the PBIX file - https://www.dropbox.com/s/p038xljt4ejghy0/community-2-pies-v2.pbix?dl=0
Here's how it works:
1. The initial table is the same but without the Q2 column. It will be added later and will be different from yours.
2. We create 2 additional tables with the help of the following [DAX] code:
Augmented Table 1 =
VAR _tbl = SELECTCOLUMNS ( 'dataset',
"Customer2", [Customer],
"Q2", [Q1] )
VAR _hugetbl = CROSSJOIN ( 'dataset', _tbl )
VAR _filter1 = FILTER ( _hugetbl, [Customer] = [Customer2] )
VAR _filter2 = FILTER ( _filter1, [Q2] <> [Q1] )
RETURN _filter2
You'll get something like this:
Customer2 is not needed here, it is used only for filtering earlier but I don't remove it in order not to make code even more complicated.
3. We create Q1 and Q2 tables which we will use in slicers:
Q1 = VALUES ( 'dataset'[Q1] )
Q2 = SELECTCOLUMNS ( VALUES ( 'dataset'[Q1] ), "Q2", [Q1] )
4. We arrange 4 connections between tables in this auxiliary model:
'Q1'[Q1] -> 'Augmented table 1'[Q1]
'Q1'[Q1] -> 'Augmented table 2'[Q2]
'Q2'[Q2] -> 'Augmented table 1'[Q2]
'Q2'[Q2] -> 'Augmented table 2'[Q1]
5. We use 'Augmented table 1' for the first pie chart and 'Augmented table 2' - for the second.
As you can see, the results are the ones you expect to obtain:
Should you need any clarifications, let me know.
@barritown thanks for the solution. it is too complicated but it seems it is working. my dataset has 2k rows and it will be fine.
@lbendlin thanks for your kind message. However, this is not what I need. I need 2 pie charts with grades. thanks again.
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
45 |