Sum dynamic parameter fields multiple selected parameter

[bjackson2760]

Here is my scenario:

I have 2 parameters with the same amount of colums and I need to be able to select from each of the 2 parameters one or more fields that will sum depending on what fields are selected.  

I need to have a column that will produce this dynamic column, no matter what the user selects from any of these 10 different parameter options.  It will sum accordingly.  I would greatly appreciate any assistance provided.  Thank you.

I am including screen shots.

****DESPITE THE DATES ON THE COLUMNS, THERE ARE NOT ACTUAL DATES**************

The columms are derived from an Excel spread sheet  that I've tried to do so many different ways.  So it is started off with tabs labeled "Jan - Dec", and the columns are Jan thru Dec within each tab.  So when I brought them in they are columns and then for each I created parameters.

Then I couldn't figure out how to make it work that way so then I have another file with all columns merged to one file (Jan - Dec tabs) in hopes of it working that way.  Same issue, I just can't figure it out.  Also, yes, I agree with the formula could go on for days, because instead of asking for the "field parameters" it was expecting columns which as you mention would be a very long conditional measure.

*****THANKS A BUNCH TO EVERYONE WHO OFFERED THERE ASSISTANCE.  I APPRECIATE YOUR TIME.  BE BLESSED!******

[v-dineshya · Accepted solution]

Hi @bjackson2760 ,

Thank you for reaching out to the Microsoft Community Forum.

I have created sample data . Please refer below snap.

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[bjackson2760]

Thanks I understand the logic, so no need for anymore assistance!

[bjackson2760]

Hi Dinesh,

Thank you so much again for providing this.  It has truly been a learning experience.  I have one question regarding your code.  Everything looks great however; I have like what will be 12( a years reports, which whole the columns Jan-Dec Numbers) of these Montly reports at which either of the columns would need to be able to be compared from one monthly finance report to another.  So with regards to the code; how do I add the rest of the files to the following code that you provided to regarding the Month Selector?  I will be renaming it to what you have as I consolidate all the files and rename it to "Fiance_2025".  But, I just wanted to get and understand what January's report would look like.  So how do I continue the code to include Feb_2025, March_2025 and the rest?

Month Selector =

ADDCOLUMNS(

    SUMMARIZE(

        Jan_2025,

        'Jan_2025'[Month]

    ),

    "Date",

        DATE(

            2025,  

            SWITCH(

                TRUE(),

                'Jan_2025'[Month]="Jan", 1,

                'Jan_2025'[Month]= "Feb", 2,

                'Jan_2025'[Month]="Mar", 3,

                'Jan_2025'[Month]= "Apr", 4,

                'Jan_2025'[Month]= "May", 5,

                'Jan_2025'[Month]= "Jun", 6,

                'Jan_2025'[Month]= "Jul", 7,

                'Jan_2025'[Month]="Aug", 8,

                'Jan_2025'[Month]= "Sep", 9,

                'Jan_2025'[Month]= "Oct",10,

                'Jan_2025'[Month]= "Nov",11,

                'Jan_2025'[Month]="Dec",12

            ),

            1

        ),

    "Month Selector", 'Jan_2025'[Month]

)

Thanks in advance!

[bjackson2760]

Thank you so much, I am working on it now...I will get back to you!

[bjackson2760]

Also, there is not only 2, there are 12 parameters for 12 months.  I just wanted to grasp the jest of what I was trying to achieve and then use it for all.  Each column from each parameter will be used to compare against the same month's month's data.  User is able to select each month they want to see for comparison from each parameter.  Each parameter is considered to be a file.  I already have that aspect working it is the interchangeable columns I need to get a "VARIANCE:" column for when comparing.

It looks to be most likely that this can't happen...

[tharunkumarRTK]

@bjackson2760  

As   @danextian  explained, you would require a lenghty dax measure if you choose field parameter approach for your scenario. I would suggest you to 

1. Unpivot your data if not already

2. Create two different diconnected tables for your slicers

3. Create a dax measures with kind of pattern

ValueMeasure = 
Var __ValuesOfDates = UNION( VALUES(DisconnectedDate1[Date]) , VALUES(DisconnectedDate2[Date])) 
Var __Result  = CALCULATE(SUM(UnpivotedData[Value]), KEEPFILTERS( TREATAS(__ValuesOfDates, UnpivotedData[Month]))) 
RETURN __Result 

4. Thats it

You will be able to achieve the expected result

You can download the report from here

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[bjackson2760]

Hi can you provide a sample report?

[bjackson2760]

FYI this is how the relationships look.  2nd try:

1st report:

[bjackson2760]

Thank you so much for the provided detail!  I will try it and get back to you! 🙂

[danextian]

Hi @bjackson2760 

I am assuming those field parameters are derived from a measure. Field parameters are essentially still individual measures which visibility is determined by a slicer selection. You will need to write a very long conditional measure to return the sum of values being selected. For example:

VAR _order =
    SELECTEDVALUE ( prametertable[field order] )
RETURN
    SWITCH (
        TRUE (),
        _order = 1, [measure 1],
        _order IN { 1, 2 }, [measure1] + [measure2],
        _order IN ...
    )

So you can imagine how long the formula would be to account for all the possible combinations.

Instead of a filed parameter, I would go for a disconnected table approach which may not be easier to setup but makes the summation of selected fields easier. 

If you want more help, please read this:

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Following the instructions will make  it easier for anyone in the community to help you.

[bjackson2760]

Hello Dane, and thank so much for responding.  I do not like asking for help, and it took me a while to do so.  I like to try and figure things out for myself, but its been 3 weeks and I said well its that time.

The columms are derived from an Excel spread sheet  that I've tried to do so many different ways.  So it is started off with tabs labeled "Jan - Dec", and the columns are Jan thru Dec within each tab.  So when I brought them in they are columns and then for each I created parameters.

Then I couldn't figure out how to make it work that way so then I have another file with all columns merged to one file (Jan - Dec tabs) in hopes of it working that way.  Same issue, I just can't figure it out.  Also, yes, I agree with the formula could go on for days, because instead of asking for the "field parameters" it was expecting columns which as you mention would be a very long conditional measure.

Thanks for the information, I will try that and get back to you!