Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX for Division not working

  • 1.  DAX for Division not working

    Bronze Contributor
    Posted Nov 26, 2020 09:39 AM
    Hello,

    Attached is the PBI and excel for your quick reference.

    PBI report has 2 table i.e. Dates Table and Report Table1.

    I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

    In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

    For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

    Once we get correct values as in column I, then I need to calculate cumulative (Column J)

    Please suggest.

    ------------------------------
    Prem Guru
    ------------------------------

    Attachment(s)

    xlsx
    Query - % Calculation.xlsx   185 KB 1 version
    pbix
    Calculate.pbix   88 KB 1 version
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 2.  RE: DAX for Division not working

    Top Contributor
    Posted Nov 26, 2020 04:09 PM
    Hi Prem,

    In your Calculateasure, you are using a COUNTA function that will return the number of rows in the date Monthly target column.

    You should replace it with SELECTEDVALUE or MAX to a single value of the monthly target :

    Calculate = DIVIDE([Completed], SELECTEDVALUE('DimDates'[Monthly Target]))


    ------------------------------
    Vincent L.
    Chartered accountant - Expert-comptable
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 3.  RE: DAX for Division not working

    Bronze Contributor
    Posted Nov 27, 2020 01:58 AM
    Edited by Prem Guru Nov 27, 2020 03:03 AM
      |   view attached
    Hi Vincent,

    I replaced the measure with the one provided by you and seem to be working, but the cumulative is not giving the correct values.

    Cumulative of Average = 
    CALCULATE(
    	[Calculate],
    	FILTER(
    		DATESYTD('DimDates'[Month End]),
    		ISONORAFTER('DimDates'[Month End], MAX('DimDates'[Month End]), DESC)
    	)
    )​


    Thanks!

    ------------------------------
    Prem Guru
    ------------------------------

    Attachment(s)

    pbix
    Calculate.pbix   88 KB 1 version
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 4.  RE: DAX for Division not working

    Bronze Contributor
    Posted Nov 27, 2020 03:06 AM
      |   view attached
    Updated PBI attached

    ------------------------------
    Prem Guru
    ------------------------------

    Attachment(s)

    pbix
    Calculate.pbix   89 KB 1 version
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 5.  RE: DAX for Division not working

    Top Contributor
    Posted Nov 27, 2020 12:11 PM
    Prem,

    Can you try using this measure :
    Cumulative of Average = VAR referencedate=MAX(DimDates[Date])
    RETURN CALCULATE([Calculate],
        FILTER(ALL(DimDates[Date]),
        DimDates[Date]<=referencedate &&
        DimDates[Date]>DATE(YEAR(referencedate)-1,12,31)
        )
    )​


    I have a few rounding differences with your Excel file.



    ------------------------------
    Vincent L.
    Chartered accountant - Expert-comptable
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 6.  RE: DAX for Division not working

    Bronze Contributor
    Posted Dec 01, 2020 02:08 AM
    Thanks Vincent, but I'm not getting the correct values for calculating the cumulative.

    I've attached excel file for your reference (highlighted table).

    Lastly when we have the correct cumulative values as per excel, then the YTD column is to be calculated by month no.

    Appreciate if you could move me to the right direction.

    ------------------------------
    Prem Guru
    ------------------------------

    Attachment(s)

    pbix
    Calculate.pbix   89 KB 1 version
    xlsx
    Output.xlsx   11 KB 1 version
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST