Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Sum a row using visible columns

I have created a table that allows a user to choose what data columns they wish to see (via field parameters).  There are up to 4 possible values they can select from.  I need to get a dynamic total ...
columns
rows
selected values
sum
visible
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    If the user selected all 4 severity levels, the goal is for the Total Count column to equal this:

    Business UnitSEV1 CYTDSEV2 CYTDSEV3 CYTDSEV4 CYTDTotal Count
    Unit 1  6410
    Unit 2  123
    Unit 3 291021
    Unit 4  224
    Unit 5 19515
    Unit 6  1 1
    Unit 7 419326
    Unit 8  3 3

     

    If the user only selected columns 2 & 4, for example, the result would be this:

    Business UnitSEV2 CYTDSEV4 CYTDTotal Count
    Unit 1 44
    Unit 2 22
    Unit 321012
    Unit 4 22
    Unit 5156
    Unit 6  0
    Unit 7437
    Unit 8  0

     

    If they selected 1 & 2, it would be this:

    Business UnitSEV1 CYTDSEV2 CYTDTotal Count
    Unit 3 22
    Unit 5 11
    Unit 7 44

     

    My field parameters slicer already takes care of the columns and rows to display.  What I need is a dynamic formula that will calculate the sum across each row based on what columns (severity levels) the user selects (or is made visible).

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Note:  The values I'm using in the field parameters are measures.  They do not directly come from a table.