Forum Discussion

gbarr12345's avatar
gbarr12345
Post Prodigy
2 years ago
Solved

Power BI - Scalar error

Hello,

 

I am trying to write a DAX query to get the sales contribution by region for the last 90 days and am getting the following error - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

 

How can I fix this? The code I wrote is below:

 

Sales Contribution by region =
VAR StartDate = TODAY() - 90
VAR EndDate = TODAY()
RETURN
    TOPN(1,
    SUMMARIZE(
        FILTER(Orders , Orders[Order Date] >= StartDate && Orders[Order Date] <= EndDate),
        Orders[Region],
        "TotalSales" , SUM(Orders[Sales])
    ),
    [Total Sales] , DESC
    )
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    lbendlin Thanks for your contribution on this thread.

    Hi gbarr12345 ,

    According to your sample data, the order data are on year 2015. Then the condition ( Orders[Order Date] >= TODAY() - 90 && Orders[Order Date] <= TODAY() ) will not return any data, that's why the measure return the blank value...

    Best Regards

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    TOPN returns a table, even if it is TOPN,1.  

     

    Return MAXX or CONCATENATEX

     

    Sales Contribution by region =
    VAR StartDate = TODAY() - 90
    VAR EndDate = TODAY()
    RETURN
        MAXX(
        TOPN(1,
        SUMMARIZE(
            FILTER(Orders , Orders[Order Date] >= StartDate && Orders[Order Date] <= EndDate),
            Orders[Region],
            "TotalSales" , SUM(Orders[Sales])
        ),
        [Total Sales] , DESC
        ),[Region])
  • gbarr12345's avatar
    gbarr12345
    Post Prodigy
    2 years ago

    Thanks for that. The error went away but now no data is appearing when I try to put it into a table visualisation:

     

    I am using a sample superstore data set to test this dax formula. 

     

    The data I'm using is below:

     

    Order DateShip DateProfitQuantity ordered newSales
    13/06/201515/06/20154390.3665126362.85
    15/06/201517/06/2015-84.43761358.68
    15/06/201516/06/201524.3121853.1
    12/06/201514/06/2015349.409117506.39
    3/06/20158/06/20151892.424193127.69
    7/06/20159/06/201522.81816216.04
    22/06/201524/06/2015-16.216303.59
    22/06/201524/06/2015-20.25651233.32
    22/06/201523/06/2015-3.3811747.31
    22/06/201523/06/2015-2.7048411.13
    2/06/20153/06/201591.95613312.59
    2/06/20153/06/2015-2.544664.4
    18/06/201522/06/2015360.2421772.56
    2/06/20152/06/2015-21.48775427.3
    2/06/20152/06/201544.6775364.75

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      lbendlin Thanks for your contribution on this thread.

      Hi gbarr12345 ,

      According to your sample data, the order data are on year 2015. Then the condition ( Orders[Order Date] >= TODAY() - 90 && Orders[Order Date] <= TODAY() ) will not return any data, that's why the measure return the blank value...

      Best Regards

      • gbarr12345's avatar
        gbarr12345
        Post Prodigy
        2 years ago

        Ah yes I understand. Apologies I should have spotted that.

         

        Thank you for your response.

         

        Is there a good alternative code instead of TODAY() - 90 to get 90 days before that date in 2015?

         

        Many Thanks.