cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HDD009 Frequent Visitor
Frequent Visitor

Calculating Gap out of Volume and agreed Volume

I tried it several times but somehow this is not working for me.

 

I have one manual source that looks like this:

SupplierAgreed Volume
Supplier A20
Supplier C15
Supplier D30

 

And the main table source that gives me information about the real volume:

SupplierVolumeMonth
Supplier A3501
Supplier B3001
Supplier C4001
Supplier D2001
Supplier A1002
Supplier A503
Supplier B502

 

Now i want to combine both to have this at the end:

SupplierVolumeAgreedGap
Supplier A502030
Supplier B35 35
Supplier C401525
Supplier D2030-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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating Gap out of Volume and agreed Volume

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.

Capture.PNG

I have attached the pbix for reference.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Calculating Gap out of Volume and agreed Volume

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.

Capture.PNG

I have attached the pbix for reference.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

HDD009 Frequent Visitor
Frequent Visitor

Re: Calculating Gap out of Volume and agreed Volume

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.

 

SupplierAgreed VolumeFrom AreaTo Area
Supplier A20NorthSouth
Supplier A15SouthWest
Supplier C15  
Supplier D30  

 

I Would filter to see what is happening in South.

So I would see the following out of the transport table:

 

SupplierVolumeMonthFrom AreaTo Area
Supplier A3501NorthSouth
Supplier B3001NorthSouth
Supplier C4001NorthSouth
Supplier D2001WestNorth
Supplier A1001SouthWest
Supplier A501EastWest
Supplier B501WestEast

 

Now i want to combine both to have this at the end by the perspective of South:

SupplierFrom AreaTo AreaVolumeAgreedGap
Supplier ANorthSouth352015
Supplier ASouthWest1015-5
Supplier BNorthSouth301515
Supplier CNorthSouth403010

 

Would this work even without agreed volume for Supplier B and C on area level?

 

Community Support Team
Community Support Team

Re: Calculating Gap out of Volume and agreed Volume

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 394 members 3,991 guests
Please welcome our newest community members: