Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
In one table I have many entries for the same NO_PAL. I added an Index column where the smaller number is the most recent entry. I want to sum "Total" of the last entry of each NO_PAL.
Thanks
Solved! Go to Solution.
Hi @FRG ,
For your requirement, you could create the below measures to achieve your desired output.
minindex = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[No_PAL])) Measure = SUMX ( FILTER ( 'Table', 'Table'[Index] = [minindex] && 'Table'[No_PAL] = MAX ( 'Table'[No_PAL] ) ), SUM ( 'Table'[Total] ) )
Here is the output.
Best Regards,
Cherry
Can you use a filter with the TOP N option? In your case, N will be 1.
Alternately, you can probably drop the date field in a filter and again use only the TOP 1 record.
Thanks for your reply!
I made this measure to calculate the sum of Total off all the min value for all NO_PAL but it's not what I'm looking for:
SUMX(Table1;CALCULATE(MIN(Table1[Total]);ALLEXCEPT(Table1;Table1[NO_PAL])))
I need the last value of all NO_PAL, wich is determined by the index column. I don't know how TopN function works correctly in this type of measure.
Thanks
@FRG wrote:Thanks for your reply!
I need the last value of all NO_PAL, wich is determined by the index column. I don't know how TopN function works correctly in this type of measure.
Thanks
From that table in your screen shot, what is the "last value of all NO_PAL, which is determined by the index column" ?
Is it 1 ?
I highlighted all the value I want to sum. It's where is the smaller index for each NO_PAL.
Thanks
Hi @FRG ,
For your requirement, you could create the below measures to achieve your desired output.
minindex = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[No_PAL])) Measure = SUMX ( FILTER ( 'Table', 'Table'[Index] = [minindex] && 'Table'[No_PAL] = MAX ( 'Table'[No_PAL] ) ), SUM ( 'Table'[Total] ) )
Here is the output.
Best Regards,
Cherry
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |