# Power BI Exchange

View Only

• #### 1.  MAX VALUE and MEASURE

Bronze Contributor
Posted Sep 03, 2020 12:39 PM
I am currently have a dataset that has rows similar to the below.

1/1/2020     5

1/1/2020     5

1/2/2020     8

1/2/2020     10

My matrix table sums those values together as displayed below:

1/1/2020     10

1/2/2020     18

I have to calculate the range for what is in the matrix not the dataset.  From what I am gathering I cannot successfully tell PowerBI to find the MAX or MIN of a measure.  I need something to find the MAX and MIN within the matrix not the dataset.  If I use the below formula it will return a value of 5 not 10.  Can anyone help with this?

This formula will give me the first number pulling the Highest value in the actual dataset.  Is there anyway to tweak this to get the 10 that I need?

Measure = calculate(MAX(NUMBER),ALL(TABLE))

------------------------------
Nick D
------------------------------

• #### 2.  RE: MAX VALUE and MEASURE

Top Contributor
Posted Sep 03, 2020 05:27 PM
Hi Nick:

Can you do a cumulative total and subtract in a date?
First Cumulative = IF(ISBLANK([Sales]), BLANK(),
CALCULATE([Total Measure], FILTER(ALLSELECTED(Dates),
Dates[Date] <= MAX(Dates[Date]))))

SECOND CUMULATIVE = CALCULATE[FIRSTCUMULATIVE] , DATEADD(Dates{Date}, -1, DAY))

Subtract them? So you are subtracting two running totals but the second one is one day behind. Just a thought.

Thanks,

Bill S.

------------------------------
William Skelley
01106
8602807221
------------------------------

• #### 3.  RE: MAX VALUE and MEASURE

Posted Sep 04, 2020 02:06 AM

Highest Sales Day =
MAXX (VALUES( dDates [Dates]), [Total Sales] )

Hi Nick, you can try out this code if it solves your problem

------------------------------
Bao Nguyen
Operations Executive
------------------------------

• #### 4.  RE: MAX VALUE and MEASURE

Posted 19 days ago
Edited by Megan Morgan 19 days ago

Hi Bao,

I've used this DAX code to create a measure which returns the highest monthly value for the measure called "% Aware" shown in the below matrix.

The DAX code below returns 56% which is perfect but I also need to get the name of the month with the highest % of awareness so that ultimately I can show something like "Highest month to date: Jul-20 56%"

Max month = MAXX (VALUES( Sheet1[Period - month]), Sheet2[% Aware] )

Any ideas on how to get the max date as well? I think it will need to be a separate measure to get the date but I just can't work it out.

Thanks,
------------------------------
Megan Morgan
Data Analysis Manager
------------------------------

• #### 5.  RE: MAX VALUE and MEASURE

Top Contributor
Posted 18 days ago

Hi Megan:

Please find attached file. I hope this helps! I added a date table as you will see.