Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

SELECTEDVALUE with SUMMARIZE.

Hi all, I know this issue has been brought up already (https://community.fabric.microsoft.com/t5/Desktop/SUMMARIZE-with-FILTER-and-SELECTEDVALUE/m-p/3034175#M1032743) but it appears no solution was given and hopefully it can get new attention by posting this again. 

 

In my case, I am gathering a lot of detailed P&L data for 3 years, which I want to aggregate at country level before all calculations in order to keep powerbi performance reasonably OK. However I want to have flexibility for the end user to select a specific reporting year. Basically I am using summarize to aggregate the data and subsequently I have many measures and user input parameters which all works fine.  However to change the year I have set up measures like for Net Sales:

Net Sales CY=
Var Param = [Param_ReportingYear]
Return
CALCULATE([Net Sales], MD_Calendar[Year] = Param)

 

And then my aggregation utilizes the summarize function referencing those measures:

Tbl_PL_byCountry =
VAR SummaryTable =
    SUMMARIZE(
        MD_Geo,
        MD_Geo[Country],
        "ctNet Sales PY", CALCULATE([Net Sales]),
        "ctSales Adjustments PY", CALCULATE([Sales Adjustments]),
        "ctGross Sales PY", CALCULATE([Gross Sales]),
        "ctOPEX PY", CALCULATE([OPEX]),
        "ctGross Profit PY", CALCULATE([Gross Profit]),
        "ctcCOGS PY", CALCULATE([cCOGS])
    )
RETURN
    SummaryTable

This works fine as long as my measure [Param_ReportingYear] is a hard coded number or for example a formula like 
YEAR(TODAY())-2
 
Ideally I would like to take the selection from a slicer like SELECTEDVALUE('ReportingYear'[UserSelection]). Unfortunately this seems not possible (missing row context?), and thus I wonder if there is a workaround? I guess this is a realistic business case that many others are dealing with. Would be interesting to hear how others set their datamodel up to accomodate this. 
 
Need Help

1 Reply

  • nsexton12's avatar
    nsexton12
    Icon for Resolver II rankResolver II
    2 years ago

    Have you tried creating a relationship between your calculated table and the table that holds your parameter for year value? In the post you linked, that seemed to resolve the issue for me. 

     

    If you include some sample data, I can test further.