Wrong column totals (table)

[ikkew]

Hi, I've been trying to display a simple table with total values but I seem to have an issue.

My totals seem to be incorrect. What could be the issue here?

Very willing to provide more info if nescecary!

Screenshot of table: https://imgur.com/a/aMMjFri

("Min" and "Totale jaarlijkse besparing" are wrong)

[themistoklis]

Try this:

Totale jaarlijkse besparing = CALCULATE(SUMX(ArtikelM, ArtikelM[Huidig]- ArtikelM[Min])  )

[ikkew]

@themistoklis Same as with the SUMX approach for the "Min" measure: the same result 

[themistoklis · Accepted solution]

Can you try this for MIN:

Min = SUMX(VALUES(Artikel[ArtikelId]), CALCULATE(SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])))

[ikkew]

This... seems to work!

Could you elaborate on why your measure

Min = SUMX(VALUES(Artikel[ArtikelId]), CALCULATE(SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])))

works, compared to

Min = SUMX(ArtikelM, ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])

I can't thank you enough though!
Same for the others who tried to help me and pointed out the source of the issue!

[themistoklis]

Generally working on Column Totals can become very tricky sometimes on PowerBI.

Values function returns the MIN of the each value in the field specified in the function. In this case 'Artikel[ArtikelID]'.

Without it 'total' takes the min across all ArtikelIDs 

[HotChilli]

Hello again,

I see that the issue is sorted now which is great.  Can you tell me what happened when you put my measure in to your pbix because i have just re-tested (fresh copy of your pbix, added MinChilli measure) and it all worked great?

[ikkew]

I'm currently refreshing my data, which takes some time (a lot of time unfortunatly +/- 2h) but I'll come back to this after it's done and probably update this reply with a screenshot. I think it's odd that it worked for you since nothing seemed to have happened on my side. Perhaps because I was trying out different things at the same time? I'm not sure..

Edit:

@HotChilli Screenshots URL

I've made screenshots of

• (themistoklis) the working statement, where 'Min' and 'Totale jaarlijkse besparing' are having the expected totals
• (HotChilli) the grayed out statement where the statement can't be processed, the visual isn't updated here since I didn't save the staement
• your statement edited to work in my .pbix, where 'Min' and 'Totale jaarlijkse besparing' are having the same totals as when I started this question, thus not changing anything in my visual

[HotChilli]

Ok, i understand now.  There is a bracket in the wrong place in your version of my measure. It should be

MinChilli = SUMX(ArtikelM, ArtikelM[Aantal] * ArtikelM[MinEenheid])

[ikkew]

You're right, my mistake and my apologies.

Your answer should've been the accepted answer since it was first.

Thank you for helping me out!

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[ikkew]

@Greg_Deckler I have updated my response, please take a look.

[v-piga-msft]

Hi @ikkew,

I think the total value shows the result of the measure Min, do you want to show the sum of 37408.8+27144+26805+25968+25872?

If it is convenient, could you share some data sample which could reproduce your scenario so that I can copy and test.

In addition, I would appreciate it if you could share your desired output.

Best Regards,

Cherry

[ikkew]

Hey @v-piga-msft, thanks for the response! Answers in bold.

I think the total value shows the result of the measure Min

Min = SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])

    I don't seem to understand what the total value of the measure would be.
    The above measure takes the total of the ordered articles
    and multiplies it by the minimum article price.
    What would the total be?

do you want to show the sum of 37408.8+27144+26805+25968+25872?

    Yes, the total should be the sum!

If it is convenient, could you share some data sample which could reproduce your scenario so that I can copy and test.

    Happy to provide data in any way!
    I feel obligated anyway since you're helping me out here!

    My data is massive though and isn't easy to sample.
    Should I just upload my database (it's test data anyway) and my .pbix file?

In addition, I would appreciate it if you could share your desired output.

    The desired output would be the sum of the value of the rows.

    So 37,408.8 + 27,144 + 26,805 + 25,968 + 25,872 = 143,197.8

I'm sorry I can't provide the insights needed to help me.
I'm still fairly new to PowerBI but try and help in any way!

EDIT: Google Drive .pbix and DB

You can restore the DB and change the connection for the .pbix in the parameters

@HotChilli Thanks for the URL! I tried to implement the example of the first linked video without result though:

TotalMin = 
VAR vNormalMin = CALCULATE( SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid]) )
VAR vTotalMin = SUMMARIZE(ArtikelM, ArtikelM[MinEenheid], "vTotalMinR", vNormalMin)

RETURN
IF ( HASONEVALUE( ArtikelM[MinEenheid] ),
    vNormalMin,
    SUMX(vTotalMin, [vTotalMinR])
)

Gives me another incorrect number.. I guess I summarize on the wrong column?

[HotChilli]

Hi, a question always has a better chance of being answered if sample data or pbix is attached.

If you share a link via dropbox, onedrive etc we can download pbix from there.  Try not to make the file too big, we just need enough test data to illustrate the problem.

If you can't share a link then paste some test data(from excel or similar) into the reply- body.  You'll get an error about incorrect html but if you hit 'post' again, it should work.

[ikkew]

@HotChilli Google Drive URL for .pbix and DB backup

[HotChilli]

Thanks for the pbix.  This will require testing but I think if you alter your measure to this

MinChilli = SUMX(ArtikelM, ArtikelM[Aantal] * ArtikelM[MinEenheid])

The rules on totals are, generally, write a different DAX formula for totals and individual table rows using HASONEVALUE 

OR re-write the measure to work for totals and rows.  That's what i tried here.

[ikkew]

@HotChilli Tried your measure (nice measure name btw), but doesn't work like expected.

1. I can't seem to use ArtikelM[MinEenheid] without the MIN() function since it won't get recognised.
2. Changing SUM(ArtikelM[Aantal]) to SUMX(ArtikelM, ArtikelM[Aantal]) doesn't seem to do much.

MinChilli = SUMX(ArtikelM, ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])

I appreciate the effort but am still no closer

[ikkew]

I do have a measure in my table, so the problem you described in your post might be the issue.

I'll check out your suggested solutions and come back to this later.

Thanks in advance!

Edit:

I've been trying to make it work but I just can't wrap my head around how to implement the solution.

My "Min" column is the following measure:

 Min = SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])

which I tried to convert as such:

MinT = IF( 
        HASONEFILTER(ArtikelM[Huidig]),
        SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid]),
        SUMX( 
            FILTER( ArtikelM, SUM(ArtikelM[Aantal]) > 0), 
                SUM(ArtikelM[Aantal]) * MIN(ArtikelM[MinEenheid])
            )
    )

But it's not working out for me.
And even if it worked, I have no idea what to do with the measure? Just replace "Min" with "MinT" in my case?

[HotChilli]

https://community.powerbi.com/t5/Community-Blog/Fixing-Total-Errors-In-Power-BI-I-Know-It-Can-Be-Fru...

Welcome to the world of powerbi.  The totals are actually correct, but they're not what you want.