itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: calculate unique values

To exclude duplicates in my calculation, either for an average, a sum or a count:

power bi

I will use this formula to calculate by category:

SUMX(FILTER(SUMMARIZE('table','table'[argument1],'table'[argument2]),EARLIER('table'[argument1])='table'
[argument1]),[argument2])

power bi
power bi

NOTE:

  • Change table and argument by yours
  • To average, replace SUMX by AVERAGEX
  • To count, replace SUMX by COUNTX

To sum the highest value by category:

SUMX(FILTER(SUMMARIZE('table','table'[argument1],'table'[argument2],"new value",MAX('table'[argument3])),EARLIER('table'[argument1])='table'[argument1]),[new value])

power bi

NOTE:

  • Change new value by yours
  • For the lowest value, change MAX by MIN
  • If I want only for ticket, I will remove problem
power bi
power bi

To sum the value based on the first date by category:

SUMX(FILTER(SUMMARIZE('table','table'[argument1],'table'[argument2],"new value",CALCULATE(MAX('table'[argument3]),'table'[argument4]=MIN('table'[argument4]))),EARLIER('table'
[argument1])='table'[argument1]),[new value])

power bi

NOTE: for the last date, change MIN by MAX

For the full table:

SUMX(FILTER(SUMMARIZE('table','table'[argument1],'table'[argument2]),'table'[argument1]='table'[argument1]),[argument2])

power bi

NOTE: to use in a measure, replace EARLIER by MIN or MAX

Interesting Topics