cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
NevilleDuToit
Frequent Visitor

Price gap calculation - All manufacturers vs. Specified manufacturer

‎08-05-2022 02:58 AM

Good day,

I'm busy with a price gap calculation using one manufacturer as the base and all other manufacturers average price compared to that specified manufacturer. Basically I need to get the average price difference of all manufacturers to the average price for manufacturer A i.e A-B, A-C, A-D etc. See below table as example of what is required.

 

Bakery(All) 
Customer Group(All) 
   
Row LabelsAVR PricePrice Gap
A                       16.13 
B                         9.83          6.29
C                       14.76          1.37
D                       15.42          0.71
E                       10.46          5.67
F                       10.87          5.26
G                       10.67          5.46
H                         9.99          6.13
I                       10.63          5.50
J                         8.04          8.08
K                         7.99          8.13
L                       12.31          3.82
M                         8.47          7.65
N                       14.46          1.67
O                       14.05          2.08
P                       12.48          3.65
Q                       11.75          4.38
R                       10.56          5.57
S                       10.64          5.48

 

The statement I'm using is as follow but I'm not getting the desired result:

 

gap test =
CALCULATE (
    AVERAGE ( 'Pricing - GT (2)'[AVR Price] ),
    FILTER ( 'Pricing - GT (2)', 'Pricing - GT (2)'[Manufacturer] = "Albany" )
)
    - CALCULATE (
        AVERAGE ( 'Pricing - GT (2)'[AVR Price] ),
        ALL ( 'Pricing - GT (2)'[Manufacturer] )
    )

 

NevilleDuToit_0-1659693319025.png

 

Below is a sample of the table I use, table is linked only to a manufacturer sort order and a date tables:

IDStart timeSales RepBakeryDepot NameBill To Number (Mosaic Account Number)Store NameTown NameCustomer Group Price ManufacturerTypeSub TypeSort OrderMeasure
2279544580Nelson Fanaye MadunaSasolburgSasolburgNathi Tuck ShopBophelongIndependent        16.13ASuperiorWhite1RSP
1521144511Nelson Fanaye MadunaSasolburgSasolburgPaulos Tuck ShopBophelongIndependent          9.83BSuperiorWhite2RSP
1521244511Nelson Fanaye MadunaSasolburgSasolburgMorning StarBophelongIndependent        14.76CSuperiorWhite3RSP
1521344511Nelson Fanaye MadunaSasolburgSasolburgZet Tuck ShopBophelongIndependent        15.42DSuperiorWhite4RSP
1521044511Nelson Fanaye MadunaSasolburgSasolburgPhola SupermarketBophelongIndependent        10.46ESuperiorWhite5RSP
2279344580Nelson Fanaye MadunaSasolburgSasolburgBophelong SupermarketBophelongIndependent        10.87FSuperiorWhite6RSP
2279444580Nelson Fanaye MadunaSasolburgSasolburgebenezer tuck shopBophelongIndependent        10.67GSuperiorWhite7RSP
2279244580Nelson Fanaye MadunaSasolburgSasolburgBig 11 SupermarketBophelongIndependent          9.99HSuperiorWhite8RSP
2279844580Nelson Fanaye MadunaSasolburgSasolburgBB storeBophelongIndependent        10.63ISuperiorWhite9RSP
2280144580Nelson Fanaye MadunaSasolburgSasolburgDarso Tuck ShopBophelongIndependent          8.04JSuperiorWhite10RSP
2279944580Nelson Fanaye MadunaSasolburgSasolburgMr SupermarketBophelongIndependent          7.99KSuperiorWhite11RSP
2280544580Nelson Fanaye MadunaSasolburgSasolburgBig Star SupermarketBophelongIndependent        12.31LSuperiorWhite12RSP
2280344580Nelson Fanaye MadunaSasolburgSasolburgDude 7BophelongIndependent          8.47MSuperiorWhite13RSP
2280244580Nelson Fanaye MadunaSasolburgSasolburgMama Africa Tuck ShopBophelongIndependent        14.46NSuperiorWhite14RSP
2279644580Nelson Fanaye MadunaSasolburgSasolburgHappiness tuck shopBophelongIndependent        14.05OSuperiorWhite15RSP
2280044580Nelson Fanaye MadunaSasolburgSasolburgMhlanga Tuck ShopBophelongIndependent        12.48PSuperiorWhite16RSP
2280444580Nelson Fanaye MadunaSasolburgSasolburgRehoboth Tuck ShopBophelongIndependent        11.75QSuperiorWhite17RSP
2279744580Nelson Fanaye MadunaSasolburgSasolburgAlex tuck shopBophelongIndependent        10.56RSuperiorWhite18RSP
898044483Nelson Fanaye MadunaSasolburgSasolburgMandela Tuck ShopBophelongIndependent        10.64SSuperiorWhite19RSP
897944483Nelson Fanaye MadunaSasolburgSasolburgMega supermarketBophelongIndependent        16.63ASuperiorWhite1RSP
897844483Nelson Fanaye MadunaSasolburgSasolburgNdlovu tuck shopBophelongIndependent        10.33BSuperiorWhite2RSP
898144483Nelson Fanaye MadunaSasolburgSasolburgRethabile tuck shopBophelongIndependent        15.26CSuperiorWhite3RSP
899144483Nelson Fanaye MadunaSasolburgSasolburgLusaka Tuck ShopBophelongIndependent        15.92DSuperiorWhite4RSP
899344483Nelson Fanaye MadunaSasolburgSasolburgZAMA ZAMA TUCK SHOPBophelongIndependent        10.96ESuperiorWhite5RSP
898944483Nelson Fanaye MadunaSasolburgSasolburgBig 11 SupermarketBophelongIndependent        11.37FSuperiorWhite6RSP
899244483Nelson Fanaye MadunaSasolburgSasolburgMJ SupermarketBophelongIndependent        11.17GSuperiorWhite7RSP
899044483Nelson Fanaye MadunaSasolburgSasolburgEbony tuck shopBophelongIndependent        10.49HSuperiorWhite8RSP
1520944511Nelson Fanaye MadunaSasolburgSasolburgDibaba Tuck ShopBophelongIndependent        11.13ISuperiorWhite9RSP
2280644580Nelson Fanaye MadunaSasolburgSasolburgGood Price SupermarketBophelongIndependent          8.54JSuperiorWhite10RSP
1520844511Nelson Fanaye MadunaSasolburgSasolburgDude Tuck ShopBophelongIndependent          8.49KSuperiorWhite11RSP
1520744511Nelson Fanaye MadunaSasolburgSasolburgMillennium Tuck ShopBophelongIndependent        12.81LSuperiorWhite12RSP
1520544511Nelson Fanaye MadunaSasolburgSasolburgLengweleng Tuck ShopBophelongIndependent          8.97MSuperiorWhite13RSP
1520344511Nelson Fanaye MadunaSasolburgSasolburgebenezer tuck shopBophelongIndependent        14.96NSuperiorWhite14RSP
1519844511Nelson Fanaye MadunaSasolburgSasolburgHappiness tuck shopBophelongIndependent        14.55OSuperiorWhite15RSP
2365344588Nelson Fanaye MadunaSasolburgSasolburgLusaka Tuck ShopBophelongIndependent        12.98PSuperiorWhite16RSP
2049344551Nelson Fanaye MadunaSasolburgSasolburgZet Tuck ShopBophelongIndependent        12.25QSuperiorWhite17RSP
2049444551Nelson Fanaye MadunaSasolburgSasolburgTropica tuck shopBophelongIndependent        11.06RSuperiorWhite18RSP
2048244551Nelson Fanaye MadunaSasolburgSasolburgBig 11 SupermarketBophelongIndependent        11.14SSuperiorWhite19RSP
2048344551Nelson Fanaye MadunaSasolburgSasolburgteddy tuck shopBophelongIndependent        16.43ASuperiorWhite1RSP
2048544551Nelson Fanaye MadunaSasolburgSasolburgBafana Bafana Tuck ShopBophelongIndependent        10.13BSuperiorWhite2RSP
2048644551Nelson Fanaye MadunaSasolburgSasolburgFifa Tuck ShopBophelongIndependent        15.06CSuperiorWhite3RSP
2048044551Nelson Fanaye MadunaSasolburgSasolburgHlatshwayo Tuck ShopBophelongIndependent        15.72DSuperiorWhite4RSP
2048744551Nelson Fanaye MadunaSasolburgSasolburgDube Tuck ShopBophelongIndependent        10.76ESuperiorWhite5RSP
2048844551Nelson Fanaye MadunaSasolburgSasolburgPaulos Tuck ShopBophelongIndependent        11.17FSuperiorWhite6RSP
2048944551Nelson Fanaye MadunaSasolburgSasolburgEbony tuck shopBophelongIndependent        10.97GSuperiorWhite7RSP
2049044551Nelson Fanaye MadunaSasolburgSasolburgMama Africa Tuck ShopBophelongIndependent        10.29HSuperiorWhite8RSP
2049244551Nelson Fanaye MadunaSasolburgSasolburgShalom SupermarketBophelongIndependent        10.93ISuperiorWhite9RSP
2363444588Nelson Fanaye MadunaSasolburgSasolburgShalom Tuck shopBophelongIndependent          8.34JSuperiorWhite10RSP
2364344588Nelson Fanaye MadunaSasolburgSasolburgMotsepe Tuck ShopBophelongIndependent          8.29KSuperiorWhite11RSP
2363644588Nelson Fanaye MadunaSasolburgSasolburgLesedi Tuck ShopBophelongIndependent        12.61LSuperiorWhite12RSP
2365144588Nelson Fanaye MadunaSasolburgSasolburgMadiba tuck shopBophelongIndependent          8.77MSuperiorWhite13RSP
2365244588Nelson Fanaye MadunaSasolburgSasolburgPaulos Tuck ShopBophelongIndependent        14.76NSuperiorWhite14RSP
2364744588Nelson Fanaye MadunaSasolburgSasolburgHappiness tuck shopBophelongIndependent        14.35OSuperiorWhite15RSP
2364944588Nelson Fanaye MadunaSasolburgSasolburgLengweleng Tuck ShopBophelongIndependent        12.78PSuperiorWhite16RSP
170144434Nelson Fanaye MadunaSasolburgSasolburgDibaba Tuck ShopBophelongIndependent        12.05QSuperiorWhite17RSP
169644434Nelson Fanaye MadunaSasolburgSasolburgRethabile tuck shopBophelongIndependent        10.86RSuperiorWhite18RSP
169844434Nelson Fanaye MadunaSasolburgSasolburgMama tuck shopBophelongIndependent        10.94SSuperiorWhite19RSP

 

Please can you assist me with this statment to be able to calculate a price gap as per the above?

 

Much appreciated,

Neville

Labels:
Message 1 of 5
258 Views
0
4 REPLIES 4
Barthel
Solution Sage
Solution Sage

‎08-05-2022 04:34 AM

Hey,

If manufacturer A is equal to 'Albany', you can use this code:

gap test =
VAR _ManufacturerA =
    CALCULATE (
        AVERAGE ( 'Pricing - GT (2)'[AVR Price] ),
        'Pricing - GT (2)'[Manufacturer] = "Albany"
    )
VAR _CurrentManufacturer =
    AVERAGE ( 'Pricing - GT (2)'[AVR Price] )
RETURN
    _ManufacturerA - _CurrentManufacturer

 

Message 2 of 5
219 Views
0
NevilleDuToit
Frequent Visitor
In response to Barthel

‎08-10-2022 01:36 AM

Hi Barthel,

Thank you for the expression, it works perfectly. I'm using this expression on another table with the same data but more to it, but it then just gives me the average pricing of each manufacturer as a negative price. How does additional fields effect the expression. What I don't have is the AVR Price column in the new table, but tried AVERAGE(Price) when pulling price on the grid. See sample table below. Would the expression also change if I use page filters for certain fields on the dashboard?

 

IDStart timeCompletion timeEmailSales RepBakeryDepot NameBill To Number (Mosaic Account Number)Store NameTown NameCustomer GroupFrom who does this store get their Albany bread?Is there a Pricing Poster in the Store?Does the Store have a Bread Stand?Is the Store GPS Plotted / Geo Coded?Is Tinkies Available in the Store?Is BOB Available in the Store?Are Buns & Rolls Available in the Store?Is Whole Wheat Brown Bread available in the Store?AttributeValuePriceManufacturerTypeSub TypeSort OrderMeasureAlbany Price
22795################Nelson.Maduna@TigerBrands.comNelson Fanaye MadunaSasolburgSasolburgNathi Tuck ShopBophelongIndependentAlbany DirectlyYesNoYesYesNoNoNoBlue Ribbon Classic White RSP16R 16.00Blue RibbonSuperiorWhite2RSP0
15211################Nelson.Maduna@TigerBrands.comNelson Fanaye MadunaSasolburgSasolburgPaulos Tuck ShopBophelongIndependentAlbany DirectlyYesNoYesYesNoNoNoBlue Ribbon Classic White RSP16R 16.00Blue RibbonSuperiorWhite2RSP0
22801################Nelson.Maduna@TigerBrands.comNelson Fanaye MadunaSasolburgSasolburgDarso Tuck ShopBophelongIndependentAlbany DirectlyYesNoYesNoNoNoNoBlue Ribbon Classic White RSP16R 16.00Blue RibbonSuperiorWhite2RSP0
37233################lucinda.september@TigerBrands.comLucinda SeptemberBellvilleBellville Mega supermarketWellingtonIndependentAlbany DirectlyYesYesYesNoNoNoNoAlbany Everyday White RSP13R 13.00AlbanyEverydayWhite1RSP13
37238################lucinda.september@TigerBrands.comLucinda SeptemberBellvilleBellville Corner Fruit & VegWellingtonIndependentAlbany DirectlyYesYesNoNoNoNoNoAlbany Everyday White RSP13R 13.00AlbanyEverydayWhite1RSP13
37757################lucinda.september@TigerBrands.comLucinda SeptemberBellvilleBellville Pick MorBelharIndependentAlbany DirectlyYesYesYesNoYesYesYesAlbany Everyday White RSP1 699.00R 16.99AlbanyEverydayWhite1RSP16.99
29323################lucinda.september@TigerBrands.comLucinda SeptemberBellvilleBellville Brackenfell SupermarketBrackenfellIndependentAlbany DirectlyNoYesYesYesYesYesYesSasko More Slices White RSP15R 15.00SaskoEverydayWhite3RSP0
31799################lucinda.september@TigerBrands.comLucinda SeptemberBellvilleBellville Die Rug SuperettePaarlIndependentAlbany DirectlyNoNoYesNoNoNoNoSasko More Slices White RSP15R 15.00SaskoEverydayWhite3RSP0
33847################lucinda.september@TigerBrands.comLucinda SeptemberBellvilleBellville Maadina Tuck ShopBelharIndependentAlbany DirectlyNoYesYesNoNoNoNoSasko More Slices White RSP15R 15.00SaskoEverydayWhite3RSP0

Your assistance is highly appreciated.

 

Regards,

Neville

Message 3 of 5
187 Views
0
v-shex-msft
Community Support
Community Support
In response to NevilleDuToit

‎08-21-2022 07:33 PM

Hi @NevilleDuToit,

These aggregation functions should be effective based on current row contents, if you change the filter effects, they will also re-calculate the results.
Row Context and Filter Context in DAX - SQLBI

If you mean to calculate with multiple aggregations, you can take a look at Greg's blog to handle this scenario:

Measure Totals, The Final Word 

Notice: please not attach the sensitive data in your reply or sample data.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 5 of 5
157 Views
0
NevilleDuToit
Frequent Visitor
In response to NevilleDuToit

‎08-16-2022 03:59 AM

Hi there Team,

I have not had a reply as yet on the above question after a solution was given. Kindly help me as I'm not getting the statement to work with additional fields added to the dataset.

 

Much appreciated,

Neville

Message 4 of 5
169 Views
0

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All