The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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):
Excel:
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:
EXCEL (with the true valor):
here the formula used:
2019:
hoping some savior helps me
thank you
best,
Dave
@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.
Hi @amitchandak
you can download the database from this link.
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
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |