Forum Discussion
Passing Slicer Value into Calculated Formula to display the results in Graph / Table
Hi I have a Scenario where i need to work on the below kinda data.
I have a data as below and one dimension table with the dimension values joined with the below fact table.
| Id | Count | Code |
| 1 | 2 | A |
| 2 | 1 | B |
| 3 | 3 | C |
| 4 | 1 | D |
| 5 | 4 | A |
| 6 | 5 | B |
| 7 | 3 | C |
| 8 | 5 | D |
| 9 | 3 | A |
| 10 | 6 | B |
So i created Calculated column for SET A, SET B, SET C, SET D as below
Qty A= CALCULATE(sum('Table1'[Count]),FILTER('Table1','Table1'[Code]="A"))
Qty B= CALCULATE(sum('Table1'[Count]),FILTER('Table1','Table1'[Code]="B"))
Qty C=CALCULATE(sum('Table1'[Count]),FILTER('Table1','Table1'[Code]="C"))
Qty D= CALCULATE(sum('Table1'[Count]),FILTER('Table1','Table1'[Code]="D"))
Im getting KPI Card displaying with ( Total = 33, Qty A = 9, Qty B = 12, Qty C = 6, Qty D = 6 )
Claim ratio for Qty A = 9 / 33, Qty B = 12/33, Qty C = 6/33, Qty D = 6/33 )
Above calculations i'm doing by creating individually for Claim Ratio.
My Intenstion is i will place one pie graph by pulling one Dimension column name ( Product Category with 5 values ) and will pull the Total Quantity and will add a Slicer Code with single selector
when i Choose A = display Qty A with 5 Product values.
when i Choose B = display Qty B with 5 Product values.
when i Choose C = display Qty C with 5 Product values.
when i Choose D = display Qty D with 5 Product values.
Where i can skips the Qty A,Qty B,Qty C,Qty D metrics by adding Total Qty , Claim Ratio n with Dimension Column name by using single value selecting from Slicer
but how to create a dynamic Calculation for Ratio when I select "A" from slicer where i need to dynamic to calulcate
Ratio = Total Qty / CALCULATE(sum('Table1'[Count]),FILTER('Table1','Table1'[Code]="A")) and need to display in the chart
Im not able to find how to calculate dynamically when i Pic A or B or C or D from slcier that value "A" or "B" or "C" or "D" need to pass to the Dax Calc Formula Ratio and need to display.
2nd ( How to make a Value "A" as default to to Slicer when i didnt select any thing.
Thanks,
Rams
Hi Anonymous,
Not really sure if I understood your question but to pass along the slicer value on a given formula you need to have something like this:
Slicer Selection = Var Select_Slicer = MIN('Table'[Code]) Return CALCULATE(SUM('Table'[Count]);'Table'[Code] = Select_Slicer)This will get the minimum value from your table you use on the slicer, if you have only one selection it will give you that number if not it will give you the minimum value on your table if the slicer is A, B, C, ... it will give A.
Believe this will solve both your issues.
Check the image below where the quantity is always the slicer value except if another is selected.
Just one question if you have the data as you show it why did you made a calculate column for each Code and not a measure that will make the calculations based on your context so if you filter out A it will return all counts of A.
I have made the following measures:
Qty = SUM('Table'[Count]) Claim Ratio = DIVIDE([Qty];CALCULATE([Qty];ALL('Table'[Code])))Based on this and on the slicer it will return the values on a single card but you can also make a filter on the visual to keep as is.
Regards,
MFelix
2 Replies
- MFelix
Super User
Hi Anonymous,
Not really sure if I understood your question but to pass along the slicer value on a given formula you need to have something like this:
Slicer Selection = Var Select_Slicer = MIN('Table'[Code]) Return CALCULATE(SUM('Table'[Count]);'Table'[Code] = Select_Slicer)This will get the minimum value from your table you use on the slicer, if you have only one selection it will give you that number if not it will give you the minimum value on your table if the slicer is A, B, C, ... it will give A.
Believe this will solve both your issues.
Check the image below where the quantity is always the slicer value except if another is selected.
Just one question if you have the data as you show it why did you made a calculate column for each Code and not a measure that will make the calculations based on your context so if you filter out A it will return all counts of A.
I have made the following measures:
Qty = SUM('Table'[Count]) Claim Ratio = DIVIDE([Qty];CALCULATE([Qty];ALL('Table'[Code])))Based on this and on the slicer it will return the values on a single card but you can also make a filter on the visual to keep as is.
Regards,
MFelix
Hi MFelix,
Thanks So much what ever i'm expecting is achiveble from your solution.
Thanks Sooo Much.
Regards,
Rams