Matrix output from a Measure that uses Virtual Tables

[mrothschild]

PowerBI file is here: https://drive.google.com/file/d/15UbEraNivloq7pkqOKLUJFhnJLxFwXCi/view?usp=sharing

Excel **output** file is here: https://docs.google.com/spreadsheets/d/15UptSqVU2ky4pDa8Ax7AkGDgfCqfL9bs/edit?usp=sharing&ouid=11511...

The desired output is a Matrix that looks like the Excel spreadsheet. 

The Excel file above was copy/pasted data from a Calculated Table in the PBIX file above.  The PBIX table is "zzzCalc Table audit".  In that table, calculations and VARs are based on "hard-coded" input. 

The Measure "zzVirtual Table Measure - Static" has the exact same programming as "zzzCalc Table audit", but outputs a Measure instead of a table. 

The Measure "zzVirtual Table Measure - Dynamic" is the programming for that I want to include in a Matrix visual.  The difference between ". . .Static" and ". . .Dynamic" is that the Dynamic version is driven by user inputs into the different Slicers on the screen.  

Not sure this is possible because of PowerBI's inability to create Calculated Tables from slicer inputs, but the data is all in there, so I'm hoping a work-around exists.

Wondering if running the Parameter What-if through a Tile Slicer that is disconnected from everything else - perhaps (throwing spaghetti against a wall) - creating a disconnected slicer to control/filter the Matrix is the workaround, but I'm not familiar how to do it?

Thanks!

[mrothschild · Accepted solution]

Here is the solution:

Measure =

VAR _StrtDate =
    EOMONTH(2023,12,1) + 1  

VAR _LeaseTermTotal = 
//    60
    _LeaseTermFirst + _LeaseTermSecond + _LeaseTermTransition

VAR _EndDate = 
    EOMONTH(_StrtDate,_LeaseTermTotal+1)

VAR _CalMth =
    GENERATESERIES (0, DATEDIFF (_StrtDate, _EndDate, MONTH ), 1  )


VAR Virtual_Table_1.0 = 
    ADDCOLUMNS(
        _CalMth,
        "Date",
            IF([Value]=0,EOMONTH(_DeploymentDate,[Value]),EOMONTH(_StrtDate,[Value]-1)+1)
    )
VAR Virtual_Table_1.1 = 
    ADDCOLUMNS(
        Virtual_Table_1.0,
        "@AcqCost_1",
            _AcquisitionPrice_First,

        "@GrossCF",
            SWITCH(TRUE()                                                                                                   ,
                [Value] = 0, -1 * _AcquisitionPrice_First                                                                   ,
                [Value] = _LeaseTermFirst + 1 && _LeaseTermTotal = _LeaseTermFirst, _ExitPrice_Scenario_First               ,
                [Value] = _LeaseTermFirst + 1 && _LeaseTermTotal <> _LeaseTermFirst,  _CashFlow_Gross_Second                ,
                [Value] = _LeaseTermTotal + 1, _ExitPrice_Scenario_Second                                                   ,
                [Value] < _LeaseTermFirst + 1, _CashFlow_Gross_First                                                        ,
                [Value] < _LeaseTermTotal + 1, _CashFlow_Gross_Second                                                       ,
                1234
            )
)

RETURN
[@GrossCF]

This provides the ability to generate a Matrix of Measures out of a virtual table driven by Parameter Slicer SELECTEDVALUES().

[lbendlin]

The desired output is a Matrix that looks like the Excel spreadsheet. 

UX design red flag right there.

Instead of all the What-If parameters  you could use the filter pane.

What is the business insight you are trying to support?

[mrothschild]

The users want to be able to input the single numbers (as in PBIX) and get the output (as in PBIX) but want to see the periodic (in this case) monthly figures.  This is a leasing template to evaluate investment returns and users insist on seeing the underlying numbers that produce the investment returns.  

Beyond that, I suspect some graphs of periodic cash flows and sensitivity/compare-contrast analysis will be requested, but until I generate the first part, the subsequent stuff is moot. 

[lbendlin]

I don't see any formulas in the Excel sheet? What's the logic for the table?

Also, what's the role of the R visual?

[mrothschild]

The Excel is a copy/paste from the calculated table that is in PBIX referenced above with "hard-coded" inputs.  Not sure which R visual you're referring to, but probably wouldn't be able to answer anyway.  R is installed for other PBIX files, and maybe this one is using "Advanced Cards" somewhere.

If I can figure out how to limit/filter/slice the "Value" on the "Duplicate of Summary Model" tab to the user input to "Lease Term (months)" slicer, I think I can brute force the Matrix with a bunch of if/thens.