How to summarize only selected columns

I have three columns in a table and we are using parameters to select the required column to the table.
Based on the selected column, I need to summarize and do some calculation.
I am stuck and unable to find how to make “VAR SUMMERISE” to work, this variable should summerize the selected columns only. I am using this to find the denominator.
It works fine when all three columns are selected, but I need to summerize based on the selected column
Parameter are [column names]: We have a slicer on the page
:ballot_box_with_check: Partnername
:stop_button: partnerTYPE
:ballot_box_with_check: partnercountry
I need a syntax to summarize just the selected columns

Uniques =
VAR sumfc = SUM(salary)
VAR SUMMARISE = SUMMARIZE(Partnername, Partnercountry, partnerTYPE, "Average", sumfc)
RETURN
    IF(
        OR(
            ISINSCOPE('Partnername'),
            OR(ISINSCOPE('Partnercountry'), ISINSCOPE(partnerTYPE))
        ),
        SUM(salary),
        DIVIDE(sumfc, COUNTROWS(SUMMARISE))
    )

Bumping this post for more visibility from our experts and users.

1 Like

Hi @christina.weber

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

@christina.weber Information provided is incomplete, so im posting different example for reference,

  1. Create a table called MonthTable consisting of only MonthNames

    MonthNames

    Jan

    Feb

    Dec

  2. Use this Column MonthName from this MonthTable as a slicer for selecting month.

  3. In your data table create a measure called SelectedMonthValue

    SelectedMonthValue = IF(HASONEFILTER(MonthTable[MonthName]),
    LOOKUPVALUE((YourTable[Amount]),YourTable[Month],Values(MonthTable[MonthName]))
    ,1)

What this does is finds the value of Amount from YourTable ( data table) for the selectedmonth in the slicer. If no value is selected in slicer it is set to 1.

  1. Now the magic

    Create a measure called Relative in YourTable.

    Relative = SUMX(YourTable,Divide(YourTable[Amount],[SelectedMonthValue]))

    What this does is it iterates YourTable row by row and then calculates the relative value for that row.

To be clear I have a similar question. Where I want to add a multiplier to this measure.

If I would like to multiply the first month by the difference of months in the selection

FILTER
Jan - May

Jan * 5
Feb * 4
March * 3
April * 2
May * 1

How could I implement that?

1 Like

This is not what I want. To avoid confusion Let me add a pbix

Sample.pbix (30.4 KB)

Hi all, here is a sample file

1 Like

Hi @Diabolica,

Thank you for taking the time to participate in this forum discussion. However, I would like to kindly remind you that if you have a question that is not directly related to the original post, it would be better to create your own thread. This way, other members and forum experts can easily see your question and provide you with more accurate and relevant responses.

Furthermore, creating a new thread is a way to show respect to the original poster and keep the discussion organized. It allows everyone to stay focused on the topic at hand and avoid confusion.

If you have any further questions or concerns, please do not hesitate to create your own thread, and I am sure that the community will be more than happy to assist you.

Thank you for your understanding.

Best regards,

Bumping this post for more visibility from our experts and users.

PBIX File already available

Hi @christina.weber,

I hope you’re doing well. I wanted to touch base regarding your inquiry in the forum.

It seems that you provided your PBIX file, but no one has responded to your request for assistance.

As a suggestion, would you be open to closing this thread and starting a new one with updated details and your PBIX file?

This way, our experts and other community members will have access to the most recent information and be able to offer potential solutions more easily.

I will be closing this thread. Please feel free to start a new thread, and we will do our best to assist you there.