Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HDD009
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
v-piga-msft
Resident Rockstar
Resident Rockstar

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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?

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.