Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I have an existing table called OK_INVOICE_ALL_EXCEL. so i have created new table called PARETO to make more lean table.
To create PARETO table i used below formula
PARETO = SELECTCOLUMNS(OK_INVOICE_ALL_EXCEL;"currency";OK_INVOICE_ALL_EXCEL[Currency Selection];"year";OK_INVOICE_ALL_EXCEL[Only Year];"month";OK_INVOICE_ALL_EXCEL[MONTH];"scenario";OK_INVOICE_ALL_EXCEL[Fiili/Bütçe];"entity";OK_INVOICE_ALL_EXCEL[FIRMA];"seller";OK_INVOICE_ALL_EXCEL[SAT_AD];"customer no";OK_INVOICE_ALL_EXCEL[CUSTOMER_NO];"customer";OK_INVOICE_ALL_EXCEL[MUS_AD];"product";OK_INVOICE_ALL_EXCEL[DESCRIPTION];"BU";OK_INVOICE_ALL_EXCEL[BU];"volume (ton)";[Volume (ton)];"Turnover";[TurnoverPV];"FP1";[FP1];"FP2";[FP2])
at the end of the results some fields does not return correct values. for instance some entities returns corrrect values and some returns totally nonsense figures
what else should i do?
Best regards,
PARETO = SELECTCOLUMNS(OK_INVOICE_ALL_EXCEL; "currency";OK_INVOICE_ALL_EXCEL[Currency Selection]; "year";OK_INVOICE_ALL_EXCEL[Only Year]; "month";OK_INVOICE_ALL_EXCEL[MONTH]; "scenario";OK_INVOICE_ALL_EXCEL[Fiili/Bütçe]; "entity";OK_INVOICE_ALL_EXCEL[FIRMA]; "seller";OK_INVOICE_ALL_EXCEL[SAT_AD]; "customer no";OK_INVOICE_ALL_EXCEL[CUSTOMER_NO]; "customer";OK_INVOICE_ALL_EXCEL[MUS_AD]; "product";OK_INVOICE_ALL_EXCEL[DESCRIPTION]; "BU";OK_INVOICE_ALL_EXCEL[BU]; "volume (ton)";[Volume (ton)]; "Turnover";[TurnoverPV]; "FP1";[FP1]; "FP2";[FP2] )
Does all columns produce inconsistant values or can you see any pattern?
Hi tex628
Not all columns returns incosistent figures but especially some entities and some products returns incosistent figures
For instance G entity returns -7099 tons whereas it should be -3825
Under that entity, all products returns incosistent values
Entitiy | Volume (ton) OK_INVOICE_EXCEL | Volume (ton) PARETO |
A | 11.124 | 11.824 |
B | 4.893 | 4.893 |
C | 1.600 | 1.600 |
D | 24 | 24 |
E | 87 | 87 |
F | 47 | 47 |
G | -3.825 | -7.099 |
Grand Total | 13.950 | 13.950 |
From what i can tell your "PARETO" table is a combination of columns from the OK_INVOICE_ALL_EXCEL table and four different measures. Is it only the measure values that display incorrect values?
"volume (ton)";[Volume (ton)]; "Turnover";[TurnoverPV]; "FP1";[FP1]; "FP2";[FP2]
No tex, all value measures returns incorrect values. as G has incorrect volumes as well as turnover, FP1 and FP2
Btw all those volume, turnover, FP1 and Fp2 is calculated new measure from existing table. not coming from original data set.
Can you provide the dax forumla for one of the four measures?
Sure,
Thank you for reaching me out btw
Volume (ton) =
CALCULATE(
SUM('OK_INVOICE_ALL_EXCEL'[Volume (t)]);
'OK_INVOICE_ALL_EXCEL'[FAT_TIP (groups)] IN { "Count" }
)
TurnoverPV = (SUM(OK_INVOICE_ALL_EXCEL[DUSD_PV])+SUM(OK_INVOICE_ALL_EXCEL[IADE_USD])+SUM(OK_INVOICE_ALL_EXCEL[dgr_gel1]))/1000
FP1 = OK_INVOICE_ALL_EXCEL[TurnoverPV]+OK_INVOICE_ALL_EXCEL[Raw Material]+OK_INVOICE_ALL_EXCEL[Packaging]+OK_INVOICE_ALL_EXCEL[Logistics]+OK_INVOICE_ALL_EXCEL[Commission]
FP2 = ([FP1]+[Depreciation Cost]+[Conversion Cost]+[Invent. Mov.])
Quick question, if you compare your PARETO table to your original table, does the row count differentiate?
Rex, both has 970.250 rows
Next question!
In this picture:
Is the OK_INVOICE_EXCEL Values from a measure while PARETO values are from a column?
volume ton is calculated measure from OK_INVOICE_EXCEL which i try to use in PARETO table with SELECTCOLUMN formula
I understand, i'm in the same spot.
I have an idea that might be the cause.
My thought is that the entities that show correct values are distinct, while those that show inconsistant values occur in more than one row.
-----
Either way, this is a rather difficult issue. is it possible for you to create a relationship between INVOICE and PARETO?
Rex i tried to create relationship between those two tables but then it says like in the picture
To create a relationship one table must have unique values in all 970,000 rows.
Do you have any column that has all unique values? Proberbly something like ProductID or something similar?
Rex i also tried that (product id, customer id etc.) still didnt work.
Also i produced all the columns in PARETO table from OK_INVOICE_EXCEL table. shouldnt it be unique since row counts are the same?
I really don't understand 😞
If you take a look at your column like this:
You can see that my Questions column has 12 unique rows while my Answers column only has 3!
See if you have any where all 970,000 rows are unique (distict) 🙂
Yes distinct counts are also same for entity names
Both same Rex
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |