Forum Discussion

jackquelynguyen's avatar
jackquelynguyen
Frequent Visitor
3 years ago

matrix and slicer

I am currently working on a project where in my table i have columns Product Option A and Product Option B which are the costs from the Base QTY column which is all 1. I want to be able to create a slicer to dynamically change my matrix and find Subtotal A and Subtotal B cost based on selected value, So if i elect 7 from the slicer, the New QTY would be 7, which is the value you selected from the slicer. The Base QTY is still 1, but now you have a new QTY value of 7 that you can use to calculate the new Subtotal A and Subtotal B costs. The measures i currently have are 

New QTY =
SELECTEDVALUE('New QTY Values'[Value], 1) * MAX('Overview'[Base QTY]) 
Subtotal A =
VAR NewQty = [New QTY]
RETURN
IF(ISBLANK(NewQty), BLANK(),
NewQty * SELECTEDVALUE('Overview'[Product Option A Cost], 1)
)
Subtotal B =
VAR NewQty = [New QTY]
RETURN
IF(ISBLANK(NewQty), BLANK(),
NewQty * SELECTEDVALUE('Overview'[Product Option B Cost], 1)
)
and I also have a table 
New QTY Values = GENERATESERIES(1,20,1). The slicer is made with the Value column from that table and all of the measures look correct so why isnt my matrix dynamically changinh? if I choose a value other than 1 the whole table goes blank
 
Content Packs
Data Modeling

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    jackquelynguyen ,
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    I think you should try like

     

    Subtotal A =
    Sumx( 'Overview', SELECTEDVALUE('New QTY Values'[Value], 1) *'Overview'[Base QTY] *'Overview'[Product Option A Cost])


    Subtotal B =

    Sumx( 'Overview', SELECTEDVALUE('New QTY Values'[Value], 1) *'Overview'[Base QTY] *'Overview'[Product Option B Cost])

    • jackquelynguyen's avatar
      jackquelynguyen
      Frequent Visitor
      3 years ago

      Here is a picture of my current report. The premise of this project is to see what the most cost efficient item is by selected value. In theory, I would like to be able to use my slicer to select a value from 1-20 to be able to see the new Subtotal A and Subtotal B Costs. I would also like to add measures for Total Cost A and Total Cost B which is their respective Subtotal Costs * 10% Contingency, 10% freight/delivery costs, and 8% of installation fees added all together. I would also like to write measures to see the subtotal costs per room by room budget to see if it is within budget, and see what the total costs are to see if it is in budget with the total budget with the costs.

      he relationship between the New QTY Values table and the Overview table is the Value column and the Base QTY column with a one to many cardinality and a cross filter on both directions, I tried the Subtotal A and Subtotal B measures you suggested, but when i select a value from the slicer other than 1 the whole table goes blank. I've also tried other formulas for Subtotal A and Subtotal B but the same problem shows up and im not sure why, the relationship between the tables are correct, but im not sure what is going wrong and why its not working, i wonder if it is because columsn Room, Item, Product Option A Description, and Product Option B Description are text. Please help i've been working on this report for a week and nothing will work. Also the measure for 

      Selected Value =

      IF(

          HASONEVALUE('New QTY Values'[Value]),

          VALUES('New QTY Values'[Value]),

          1

      )

    • jackquelynguyen's avatar
      jackquelynguyen
      Frequent Visitor
      3 years ago

      I also wrote new measures Product Option A Dynamic Cost and Product Option B Dynamic Cost, in case the problem was due to Product Option Cost A and B were static columns 

      Product Option B Dynamic Cost =
      CALCULATE(
          SUM('Overview '[Product Option B Cost]),
          'Overview '[Base QTY] = SELECTEDVALUE('New QTY Values'[Value])
      ) 
      Product Option A  Dynamic Cost =
      CALCULATE(
          SUM('Overview '[Product Option A Cost]),
          'Overview '[Base QTY] = SELECTEDVALUE('New QTY Values'[Value])
      ) 
      but again when I select a value other than 1 the Selected Value remains at the default of 1 and the rest of the column is blank.