Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I tried it several times but somehow this is not working for me.
I have one manual source that looks like this:
Supplier | Agreed Volume |
Supplier A | 20 |
Supplier C | 15 |
Supplier D | 30 |
And the main table source that gives me information about the real volume:
Supplier | Volume | Month |
Supplier A | 35 | 01 |
Supplier B | 30 | 01 |
Supplier C | 40 | 01 |
Supplier D | 20 | 01 |
Supplier A | 10 | 02 |
Supplier A | 5 | 03 |
Supplier B | 5 | 02 |
Now i want to combine both to have this at the end:
Supplier | Volume | Agreed | Gap |
Supplier A | 50 | 20 | 30 |
Supplier B | 35 | 35 | |
Supplier C | 40 | 15 | 25 |
Supplier D | 20 | 30 | -10 |
I made a relation between the suppliers.
For the volume i created a new measure that sums the volume per supplier.
Now I tried to create a new measure to calculate the gap, but unfortunately I can't select the agreed in the formular.
Same issue if I calculate the performance and want to show the gap in percentage by providing the agreed performance target.
Could you please give me an advise how to solve this?
Solved! Go to Solution.
Hi @HDD009 ,
To get your desired output, you could create a calcualted table and a measure.
Table = SUMMARIZE ( 'Table2', 'Table2'[Supplier], Table1[Agreed Volume], "Volum", MAX ( 'Table2'[Volume] ) ) Measure = CALCULATE ( MAX ( 'Table'[Volum] ) - CALCULATE ( MAX ( 'Table'[Agreed Volume] ) ) )
Here is the output.
I have attached the pbix for reference.
Best Regards,
Cherry
Hi @HDD009 ,
To get your desired output, you could create a calcualted table and a measure.
Table = SUMMARIZE ( 'Table2', 'Table2'[Supplier], Table1[Agreed Volume], "Volum", MAX ( 'Table2'[Volume] ) ) Measure = CALCULATE ( MAX ( 'Table'[Volum] ) - CALCULATE ( MAX ( 'Table'[Agreed Volume] ) ) )
Here is the output.
I have attached the pbix for reference.
Best Regards,
Cherry
Hi @v-piga-msft ,
Thank you very much! This was solving it all!
But how to solve it if i want to filter for areas now? Because the calculation would't filter on area level (North/East/South/West). The calculation would only say 20 in general is agreed and always calculate the gap for that.
Supplier | Agreed Volume | From Area | To Area |
Supplier A | 20 | North | South |
Supplier A | 15 | South | West |
Supplier C | 15 | ||
Supplier D | 30 |
I Would filter to see what is happening in South.
So I would see the following out of the transport table:
Supplier | Volume | Month | From Area | To Area |
Supplier A | 35 | 01 | North | South |
Supplier B | 30 | 01 | North | South |
Supplier C | 40 | 01 | North | South |
Supplier D | 20 | 01 | West | North |
Supplier A | 10 | 01 | South | West |
Supplier A | 5 | 01 | East | West |
Supplier B | 5 | 01 | West | East |
Now i want to combine both to have this at the end by the perspective of South:
Supplier | From Area | To Area | Volume | Agreed | Gap |
Supplier A | North | South | 35 | 20 | 15 |
Supplier A | South | West | 10 | 15 | -5 |
Supplier B | North | South | 30 | 15 | 15 |
Supplier C | North | South | 40 | 30 | 10 |
Would this work even without agreed volume for Supplier B and C on area level?
Hi @HDD009 ,
It seems that you have another requirement, you'd better create another topic so that people who may have the same question can get the solution directly.
In addition, if you solved the problem for this topic, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |