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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kala2
Helper III
Helper III

Distinct and Sum multiple columns

I want to distinct rows and the sum each row certain columns:

 

kala2_0-1627637873717.png

 

So i want to distinct the rows by Projekt ID and then sum the columns:

OFFEN - in Verzug

OFFEN - planmäßig

OFFEN - Start überfällig

IN ARBEIT - in Verzug

IN ARBEIT - planmäßig,

ABGESCHLOSSEN

 

I have this measure now for one column: 

 

SumDistinctPT = SUMX(DISTINCT('Table'[Projekt ID]), FIRSTNONBLANK('Table'[ABGESCHLOSSEN], 0))
which returns 24 result correctly

Expected sum to return for all distincted rows is: 133 (Sum of all distincted rows of the specific columns)
 
Any ideas how to distinct and sum multiple columns?
 
1 ACCEPTED SOLUTION
ebeery
Solution Sage
Solution Sage

Hi @kala2, how about something like the below.

SumDistinctAllColumns =
VAR _DistinctTable =
    SUMMARIZECOLUMNS (
        [OFFEN - in Verzug],
        [OFFEN - planmäßig],
        [OFFEN - Start überfällig],
        [IN ARBEIT - in Verzug],
        [IN ARBEIT - planmäßig],
        [ABGESCHLOSSEN]
    )
VAR _Result =
    SUMX (
        _DistinctTable,
        [OFFEN - in Verzug] + [OFFEN - planmäßig] + [OFFEN - Start überfällig] + [IN ARBEIT - in Verzug] + [IN ARBEIT - planmäßig] + [ABGESCHLOSSEN]
    )
RETURN
    _Result

View solution in original post

2 REPLIES 2
ebeery
Solution Sage
Solution Sage

Hi @kala2, how about something like the below.

SumDistinctAllColumns =
VAR _DistinctTable =
    SUMMARIZECOLUMNS (
        [OFFEN - in Verzug],
        [OFFEN - planmäßig],
        [OFFEN - Start überfällig],
        [IN ARBEIT - in Verzug],
        [IN ARBEIT - planmäßig],
        [ABGESCHLOSSEN]
    )
VAR _Result =
    SUMX (
        _DistinctTable,
        [OFFEN - in Verzug] + [OFFEN - planmäßig] + [OFFEN - Start überfällig] + [IN ARBEIT - in Verzug] + [IN ARBEIT - planmäßig] + [ABGESCHLOSSEN]
    )
RETURN
    _Result

Thanks! working perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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