cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidi4524 Member
Member

calculate quants between years

i have a query from sql server.

the columns is:

custname

month

year

partname

totalquant

if i whant to select in power bi custname, and select partname and bench between years 2016 and 2017 by total quant

 

somting like this:

custname -    year   -     partname      -  totalquant 2016     - totalquant 2017      - over/under

"microsoft"    2016        "iphone 7"     -     100                    -        180                   -        80

 

what do i need to do??

 

thanks.

5 REPLIES 5
dedelman_clng New Contributor
New Contributor

Re: calculate quants between years

The following 3 measures should work for you.  When placed in a visual they should respond to the row filters (customer, product)

 

Total Quant 2016 = 
SUMX ( FILTER (ALL(Table), Table[Year] = 2016 ), Table[totalquant])

Total Quant 2017 = 
SUMX ( FILTER (ALL(Table), Table[Year] = 2017 ), Table[totalquant])

Over/Under = [Total Quant 2017] - [Total Quant 2016]

 

If this doesn't work, please provide some mocked up raw data for further evaluation.

Hope this helps,

David

davidi4524 Member
Member

Re: calculate quants between years

hi , first thanks to the responde.

the measure is not respond to the filter , not for custname filter and not to partname filter.

what to do to fix this?

thanks.

v-huizhn-msft Super Contributor
Super Contributor

Re: calculate quants between years

Hi @davidi4524,

You can create calculated column using the following formula, use year and it as filter.

amount in one year=CALCULATE(SUM(Table[Totalquant]),ALLEXCEPT(Table,Table[year]))


Then please create the measure get difference between year.

If this is not want you want, please post some sample data for further analysis.

Best Regards,
Angelia

davidi4524 Member
Member

Re: calculate quants between years

and if i need the amount for each year by month? 

v-huizhn-msft Super Contributor
Super Contributor

Re: calculate quants between years

Hi @davidi4524,

Please try the following formula and check if it works fine.

amount in one year=CALCULATE(SUM(Table[Totalquant]),ALLEXCEPT(Table,Table[year],Table[month]))

Best Regards,
Angelia

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,003 guests
Please welcome our newest community members: