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
JWE
Helper I
Helper I

Sum of Measure wrong

Hi all

 

I need support in summarize a measure in my visualisation.

 

My scenario: I have allready merged three fact tables into one - called 'Fakten'.

I have further a target table ('Ziele') for our sales department. I need to calculate the sales amount for the assigned customers to a sales person, but sometimes for a customer-number sometimes for a customer-group.

 

So I defined a special colum to build my relationsship - see picture.

Both are inactive because I need the active relationship as default for other results.

 

Relationship.jpg

 

I created two measures which work fine for me:

 

Measure 1 for sales amount for customer number:

! Umsatz Deb =

CALCULATE (

   SUM ( Fakten[Betrag] );

   USERELATIONSHIP ( Ziele[PK_VBG]; Fakten[PK_VDeb] )

)

Measure 2 for sales amount customer group: ! Umsatz DebGrp =

CALCULATE (

   SUM ( Fakten[Betrag] );

   USERELATIONSHIP ( Ziele[PK_VBG]; Fakten[PK_VDebGrp] )

)

 

And now the problem: I want to display all results into a visualisation in one column, so far so good - but the automatically sum at the bottom is wrong.

 

Measure for this:
! Umsatz GiveMe5 =

IF (

   '1_Measures'[! Umsatz Deb] <> 0;

   '1_Measures'[! Umsatz Deb];

   '1_Measures'[! Umsatz DebGrp]

)

 

Sum wrong.jpg

 

Thanks for help.

 

Cheers Jorg

2 ACCEPTED SOLUTIONS

Hi ghuys

 

thanks for help, but nothing works nor a-"isfiltered" neither b-"sumx".

Result of a and variation mostly shows nothing!?
B-Sumx shows me the same values without correct sum.

 

BUT... the hint to make an addition works. I suprised because I thougt of filtercontext etc.

So my solution now is very simple, here combinded in one measure:

Umsatz GiveMe5 =
CALCULATE (
    SUM ( Fakten[Betrag] );
    USERELATIONSHIP ( Ziele[PK_VBG]; Fakten[PK_VDeb] )
)
    + CALCULATE (
        SUM ( Fakten[Betrag] );
        USERELATIONSHIP ( Ziele[PK_VBG]; Fakten[PK_VDebGrp] )
    )

View solution in original post

Hi @JWE,

 

Brilliant solution!Smiley LOL

 

In addition, could you accept your reply as solution to help others who may have similar issue easily find the answer and close this thread?

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

Hi @JWE,

 

In addition, could you try the formula below to see if it works?Smiley Happy

 

! Umsatz GiveMe5 =
SUMX (
    'Fakten';
    IF (
        '1_Measures'[! Umsatz Deb] <> 0;
        '1_Measures'[! Umsatz Deb];
        '1_Measures'[! Umsatz DebGrp]
    )
)

 

Regards

Hi ghuys

 

thanks for help, but nothing works nor a-"isfiltered" neither b-"sumx".

Result of a and variation mostly shows nothing!?
B-Sumx shows me the same values without correct sum.

 

BUT... the hint to make an addition works. I suprised because I thougt of filtercontext etc.

So my solution now is very simple, here combinded in one measure:

Umsatz GiveMe5 =
CALCULATE (
    SUM ( Fakten[Betrag] );
    USERELATIONSHIP ( Ziele[PK_VBG]; Fakten[PK_VDeb] )
)
    + CALCULATE (
        SUM ( Fakten[Betrag] );
        USERELATIONSHIP ( Ziele[PK_VBG]; Fakten[PK_VDebGrp] )
    )

Hi @JWE,

 

Brilliant solution!Smiley LOL

 

In addition, could you accept your reply as solution to help others who may have similar issue easily find the answer and close this thread?

 

Regards

dedelman_clng
Community Champion
Community Champion

PowerBI (and DAX) calculates the measure in the filter context of the specific row.  So while in Excel you can have a "Grand Total" that is the sum of all of the other values, the Total in a PowerBI visualization doesn't behave the same way.  Since at the total level [! Umsatz Deb] is non-zero, that is the formula you are going to get.

 

You will have to experiment with wrapping the calculation in [! Umsatz DebGrp] with something that examines the filter context of the current row (like ISFILTERED).  Try something like this:

 

IF ( NOT( ISFILTERED ( [DebNbr] ) ), [your current measure definition], [measure plus measure] )

 

Hope this helps,

David

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.