Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 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
    Longmeadow MA
    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.

    https://drive.google.com/file/d/1mM0OrhM09S2I69Vc6pIn5B9RZkJ4x1lw/view?usp=share_link



    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------