Referencing a Parameter field value in a measure

[jspwilli]

Hi,

I'm using the new parameters field to create dynamic slicers.

I have a matrix visual using a distinctcount of ID with 2 Parameters as the rows and columns of the matrix.

This works great for distinct count, however I'd like to calculate % of row total or % of column total (without using show value as).

If the rows were static I would just
DIVIDE(
       DISTINCTCOUNT'Table'[nameid]

       ,CALCULATE(DISTINCTCOUNT'Table'[nameid],ALLEXCEPT('Table','Table'[Flag Has Email])
) etc

but I can't do that as I have 39 fields in the parameter.

Is there a way to calculate the total for a colum or row in a parameter using explicit dax?

Thanks

J

[v-cgao-msft]

Hi @jspwilli ,

Since the total number of rows does not contain a filtering context from the rows, you may consider using IF() + ISFILTERED() to output a different value.

These are my three measures.

Measure1 = 
VAR _sum = 
    SUM('financials'[Profit])
VAR _total = 
    CALCULATE(
        SUM('financials'[Profit]),
        ALLEXCEPT('financials','financials'[Product],'financials'[Discount Band])
    )
VAR _result = 
    DIVIDE( _sum, _total )
RETURN
    _result

Measure2 = 
CALCULATE(
        SUM('financials'[Profit]),
        ALLEXCEPT('financials','financials'[Product],'financials'[Discount Band])
    )

Measure = 
IF(
    ISFILTERED('financials'[Segment])
    || ISFILTERED('financials'[Country]),
    [Measure1],
    [Measure2]
)

Result:

You can modify the code according to the actual data.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[jspwilli]

Hi Gao,

Thanks a lot for looking at this.

Not quite the solution I'm heading towards but I've built out your report to show what I'm trying to do.
The two parameters in place contain all the same fields - so the user can cross reference as they please - but this is where the issue is.
I've got a slicer in place that the user can select their metric type; volume, row %, column %, total %.
Volume and total % is fine to calculate but differentiating between row % and column % is where I'm stuck.
I've attached a pbi file so you can see.

MatrixMeasure = 
VAR MetricSelection = SELECTEDVALUE(Metrics[Column1])
VAR RowTotal = CALCULATE([Sum_Sales],ALLEXCEPT(financials,financials[Segment]))
VAR ColTotal = CALCULATE([Sum_Sales],ALLEXCEPT(financials,financials[Country]))
VAR TotalSales = CALCULATE([Sum_Sales],ALL(financials))

RETURN
SWITCH(
    TRUE()
    ,MetricSelection = 1, [Sum_Sales]                           --Sales
    ,MetricSelection = 2, DIVIDE([Sum_Sales],RowTotal)          --Sales for the row in the matrix (currently only works when Parameter1 = Segment
    ,MetricSelection = 3, DIVIDE([Sum_Sales],ColTotal)          --Sales for the column in the matrix (currently only works when Parameter2 = Country
    ,MetricSelection = 4, DIVIDE([Sum_Sales],TotalSales)        --Total Sales for the whole financials table
)

What I want to do is have the ALLEXCEPT argument for the Row % be whatever is field chosen for Parameter 1 and respectively the same with Column % and Parameter 2. Obviously as there is overlap between the fields, I can't be explicit in the DAX.
Currently it just works for segment in parameter 1 and country in parameter 2 but it needs to be any of the parameter fields.
If I could write these as DAX, this is exactly what would work;

Does this make sense?
Here's the pbix  let me know if the link doesn't work

[jspwilli]

I basically want to be able to do something like this:

RowTotal = CALCULATE([Sum_Sales],ALLEXCEPT(financials,Parameter1[Parameter1]))

Where the field changes in the caculation based on whatever is selected in the parameter

[jspwilli · Accepted solution]

Hi all,

I was able to work a solution out for this. Thanks to SQLBI once again.
In the pbix I attached earlier, I can calculate Row Total and Row % with 2 parameters by using the following article.
https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/

My DAX for Row % looks like this:

Row % = 
VAR ParameterValue = 
SELECTCOLUMNS(
    SUMMARIZE(Parameter1,Parameter1[Parameter1],Parameter1[Parameter1 Fields])
    ,'Parameter1'[Parameter1]
)
VAR RowTotal = 
SWITCH(
    TRUE()
    ,ParameterValue = "Segment", CALCULATE([Sum_Sales],ALLEXCEPT(financials,financials[Segment]))
    ,ParameterValue = "Country", CALCULATE([Sum_Sales],ALLEXCEPT(financials,financials[Country]))
    ,ParameterValue = "Product", CALCULATE([Sum_Sales],ALLEXCEPT(financials,financials[Product]))
    ,ParameterValue = "Discount Band", CALCULATE([Sum_Sales],ALLEXCEPT(financials,financials[Discount Band]))
)

RETURN
DIVIDE([Sum_Sales],RowTotal)

To get some of the other functionality I mentioned above I will replacing the MatrixSelection measure to select the Row % measure when = 2

MatrixMeasure = 
VAR MetricSelection = SELECTEDVALUE(Metrics[Column1])
VAR TotalSales = CALCULATE([Sum_Sales],ALL(financials))

RETURN
SWITCH(
    TRUE()
    ,MetricSelection = 1, [Sum_Sales]                           --Sales
    ,MetricSelection = 2, [Row %]          --Sales for the row in the matrix 
    ,MetricSelection = 3, [Column %]          --Sales for the column in the matrix
    ,MetricSelection = 4, DIVIDE([Sum_Sales],TotalSales)        --Total Sales for the whole financials table
)

Hope someone finds it useful