cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Grand total measure is 0 for IF forumla with 0 and 1 as results

Hello,

 

I created a measure using "if" DAX formula and result is "0" or "1". Issue is that even if the result format is considered as a number by Power BI Desktop, the grand total of this measure is not calculated. I requested some help on the forum and to my Company BI IT Service and no one found a solution. This way of working is not very academic but very simple (kind of things we use to do on Excel and works perfectly), and my feeling is that more simple things are, better they are. And I can't go ahead on my report because no workaround.

Can you please fix this ? In the following screenshot relevant measure is "IC Actif 1"

Capture.JPG 

6 REPLIES 6
Community Support
Community Support

Re: Grand total measure is 0 for IF forumla with 0 and 1 as results

Hi @Anonymous,

 

You can create a calculated column with:

 

Column= IF(SUM(Table1[Column2])-SUM(Table1[Column1])<0 || ISBLANK(SUM(Table1[Column2])),0,1)

 

Then set aggregation with SUM when the column put in the table visual. 

 

z1.PNGz2.PNG

 

Best Regards,
Qiuyun Yu 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User I
Super User I

Re: Grand total measure is 0 for IF forumla with 0 and 1 as results

Can you try:

Totals for Actif IC 1 = SUMX('table' [fullname], [Actif IC 1])



See if that works,

Regards,
Paul




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Re: Grand total measure is 0 for IF forumla with 0 and 1 as results

Hi @v-qiuyu-msft,

 

Thanks for your reply, but I can't set the aggregation, the only choices I have are "no calculation" or "% of grand total". I maybe did not precize that "nbre Opp" and "Obj. IC" are measures.

 

Anyway I change the formula of "Actif IC" to that one (to avoid measure usage) :  Actif IC 1 = IF(DISTINCTCOUNT(FaitsOppC[OpportunityId])-SUM('Objectifs IC'[Objectif])<0 || DISTINCTCOUNT(FaitsOppC[OpportunityId])<1;0;1)

 

I still can't aggregate.

Anonymous
Not applicable

Re: Grand total measure is 0 for IF forumla with 0 and 1 as results

Hello @PaulDBrown,

 

The column total is calculated but incorrect. On the whole tab grand total result is 10 instead 1 163.

 

Maybe because there 12 lines with different results for a single fullname value. See example on screenshot, where you can see that total is 0 when using SUMX on "DumUser" table (where we can find "fullname").

 

With SUMX on "DumDate" table (where we can find "fullname"), the meaure is directly incorrect (see screenshot)

 

dimd.JPGdimu.JPG

 

Highlighted
Anonymous
Not applicable

Re: Grand total measure is 0 for IF forumla with 0 and 1 as results

Hello @PaulDBrown,

 

The column total is calculated but incorrect. On the whole tab grand total result is 10 instead 1 163.

 

Maybe because there 12 lines with different results for a single fullname value. See example on screenshot, where you can see that total is 0 when using SUMX on "DumUser" table (where we can find "fullname").

 

With SUMX on "DumDate" table (where we can find "fullname"), the meaure is directly incorrect (see screenshot)

 

dimd.JPGdimu.JPG

 

Vvelarde Community Champion
Community Champion

Re: Grand total measure is 0 for IF forumla with 0 and 1 as results

@Anonymous

 

Hi, Try using thess Functions:

 

HASONEVALUE, SUMX and SUMMARIZE.

 

Can't show you the exact solution because don't know if Month, Activite IC and FullName coming from same or different tables and the relatioship between this.

 

If you can post a Dummy PBIX the solution will be more precisely.

 

Regards

 




Lima - Peru

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors