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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help to obtain yearly results. The formula doesn't go for the data filtered

Hi all,

 

I need help for this formula of yearly results 2019 to 2020.

 

I have to matching the results in BI with the results in excel.

 

Here the situation without applying filters in BI, as you see we the results in BI and excel matching. So, from this point all si ok! (highlighted in yellow the values to observe):

BI SCREEN ISSUE SCREEN.png

Excel:

 

EXCEL DATI PROVA.png

Here belowe the issue, that shows the valors doesn't match:

 

Thee filter applied are FROZEN FOOD in "CATEGORIA" and FISH in "TIPO" (all the date linked to the problem are highlighted in yellow):

BI:

BI - PROBLEMA DETTAGLIO.png

EXCEL (with the true valor):

EXCEL - PROBLEMA DETTAGLIO.png

 

here the formula used:

2019:

facing 2019 = CALCULATE ([QUOTA FACING],FILTER(BD_TOTALE_TREND, BD_TOTALE_TREND[ANNO]=19))
 
2020:
facing 2020 = CALCULATE ([QUOTA FACING],FILTER(BD_TOTALE_TREND, BD_TOTALE_TREND[ANNO]=20))
 
could be an error in the formula of QUOTA FACING? (HERE BELOW ITS FORMULA:
 
QUOTA FACING =  SUM(BD_TOTALE_TREND[facing]) / CALCULATE(SUM(BD_TOTALE_TREND[facing]), ALLEXCEPT(BD_TOTALE_TREND, BD_TOTALE_TREND[ANNO], BD_TOTALE_TREND[PERIODO], BD_TOTALE_TREND[REGIONE], BD_TOTALE_TREND[COMUNE], BD_TOTALE_TREND[INDIRIZZO], BD_TOTALE_TREND[CEDIS], BD_TOTALE_TREND[GRUPPO],BD_TOTALE_TREND[INSEGNA], BD_TOTALE_TREND[STORE_FORMAT]))

 

hoping some savior helps me

 

thank you

 

best,
Dave

 

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Any reason to use ALLEXCEPT, you almost have everything there

Is this not working

QUOTA FACING = SUM(BD_TOTALE_TREND[facing]) / CALCULATE(SUM(BD_TOTALE_TREND[facing]),all( BD_TOTALE_TREND))

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi @amitchandak 

 

you can download the database from this link.

 

https://we.tl/t-yorrmwIM4B

 

So, in the column "PRODUTTORE", the valors that correspond to our valor highlighted in yellow (before) are with the name of PROD.4.

 

To obtain the facing, as you've seen, I go for the sum of  the facing valor in the column "FACING". But, to obtain the facing quote I used this formula:


QUOTA FACING =  SUM(BD_TOTALE_TREND[facing]) / CALCULATE(SUM(BD_TOTALE_TREND[facing]), ALLEXCEPT(BD_TOTALE_TREND, BD_TOTALE_TREND[ANNO], BD_TOTALE_TREND[PERIODO], BD_TOTALE_TREND[REGIONE], BD_TOTALE_TREND[COMUNE], BD_TOTALE_TREND[INDIRIZZO], BD_TOTALE_TREND[CEDIS], BD_TOTALE_TREND[GRUPPO],BD_TOTALE_TREND[INSEGNA], BD_TOTALE_TREND[STORE_FORMAT]))

 

in the previous images I showed you that it doesn't work filtring CATEGORIA and TIPO but without applying filter the valor match with the excel.

 

hoping be clear and if you need other info please dm me

 

thank you again for your support!

 

Best,
Dave

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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