cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ash1
Frequent Visitor

Sum of values per server, for the latest date available

Hello,

 

I'm trying to create a similar chart as we create on Pivot chart on Excel. Can someone help me with the dax for it.

 

ServerDatecategoryvalue
A5/4/2017bike10
A5/3/2017car20
A5/2/2017Bus30
B5/4/2017bike20
B5/3/2017car30
B5/2/2017Bus20
C5/4/2017bike40
C5/3/2017car50
C5/2/2017Bus60

 

What I'm looking for is 

sum of values per server, for the latest date available. If I select multiple servers, powerbi should show me categories per server as  side by side bar chart

 

here is how the output should looking like. Unfortunately I cannot copy the image so I'm pasting the pivottable from Excel

 

Date5/4/2017
  
Row LabelsSum of value
A10
bike10
B20
bike20
C40
bike40
Grand Total70

 

 

Here is the DAX I'm using but however when I select multiple servers, instead of showing seperate values for each servers, it is adding up all the values 

 

Measure = CALCULATE (SUM(values), FILTER(Table, Table[date] = MAX(Table[date])))

 

Note: The reason, I'm using max date is that I have data for every hour. 

 

1 REPLY 1
spuder Resolver IV
Resolver IV

Re: Sum of values per server, for the latest date available

Hi Ash1,

 

when I use the new Matrix Visual. The result without DAX is as follows.

 

You can add the date column as a Filter (or if wished in the Column part of the matrix.

 

example.jpg

 

 

 

If you don't want to manually filter the date. There is another way.

 

You can use the date column in the visual filter level. Choose Filter Type Top N and use last date.

 

Unfortunately it is German language but it should be similar in yours.

 

example.jpg

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors