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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SonyT
Frequent Visitor

HASONEVALUE across 2 time periods

Hi, I'm still new to PowerBI and still learning how to write measures.
Currently I'm unable to get my grand totals to work.  I have tried using HASONEVALUE but because it is comparing 2 time periods the way I'm writing it doesn't seem to work.  
 
I have the following calculations (where CY = current year, LY = last year) at customer level:
 
Diff to LY TotalSales = TotalSales CY - TotalSales LY  
Value = IF (ISBLANK([TotalSales LY]), BLANK(), (([Price CY] - [Price LY]) * [Units CY Total]))
Volume = [Diff to LY TotalSales] - [Value]
 
The Volume column is calculating correctly at row level ie customer level however the grand total at the bottom of the column is not correct.
So I then tried to use the HASONEVALUE function but it doesn't work in the case where a customer was present last year but is not present this year.  How do I get the grand total to work?  This is the measure I tried to write.
 
Volume by Customer = if (HASONEVALUE(Table 1[ContactGenericName]), [Volume],
SUMX( VALUES(Table 1[ContactGenericName]),[Volume]))
 
The table looks like this: 
Pic.JPG
 
Hope this is an easy question for someone. Many thanks.
2 REPLIES 2
YJ
Resolver II
Resolver II

Can you provide the pbix file, if you need to mask out any sensentive info

regards

SonyT
Frequent Visitor

Hi, 

 

Sorry, it's an enormous file with too much info to remove.

 

Can I provide you with something else to help?

 

Thank you.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.