Forum Discussion
Dynamic Ratio creator
Hello all,
I am new to community. Please kindly help! I would like to select the "Field" in slicer and create a ratio. Not sure I need to duplicate the "Field" to have two different slicers as well. Thanks.
Data source
Is this ok ?
Date Field value Jan-23 A 45 Jan-23 B 46 Jan-23 C 47 Jan-23 D 140 Jan-23 E 10 Feb-23 A 49 Feb-23 B 11 Feb-23 C 23 Feb-23 D 41 Feb-23 E 39 Mar-23 A 40 Mar-23 B 55 Mar-23 C 39 Mar-23 D 45 Mar-23 E 105 Example 2 from Slicer Jan-23 Feb-23 Mar-23 E 10 39 105 A 45 49 40 Ratio (E/A) 0.2222 0.7959 2.6250 from Slicer Jan-23 Feb-23 Mar-23 A 45 49 40 C 47 23 39 Ratio (A/C) 0.9574 2.1304 1.0256 For your reference.
Step 0: I use these DATA below.
Step 1: I make two tables and four measures below.
<Tables>
Field01 = SUMMARIZE('DATA','DATA'[Field])Field02 = SUMMARIZE('DATA','DATA'[Field])<Measures>Field01_Sel = SELECTEDVALUE(Field01[Field])Field02_Sel = SELECTEDVALUE(Field02[Field])value Sel = SUMX(FILTER('DATA','DATA'[Field]=[Field01_Sel]),'DATA'[value])+SUMX(FILTER('DATA','DATA'[Field]=[Field02_Sel]),'DATA'[value])Ratio = DIVIDE(SUMX(FILTER('DATA','DATA'[Field]=[Field01_Sel]),'DATA'[value]),SUMX(FILTER('DATA','DATA'[Field]=[Field02_Sel]),'DATA'[value]))Step 2: I make two slicers and a matrix below.<Ratio=A/C><Ratio=E/A>
Hi izzysoon
thanks. Anyway I would say mickey64 gave you a perfect answer.
Best
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
I don't know how to move the Ratio under the value Sel.
It is easy making a line chart.
6 Replies
For your reference.
Step 0: I use these DATA below.
Step 1: I make two tables and four measures below.
<Tables>
Field01 = SUMMARIZE('DATA','DATA'[Field])Field02 = SUMMARIZE('DATA','DATA'[Field])<Measures>Field01_Sel = SELECTEDVALUE(Field01[Field])Field02_Sel = SELECTEDVALUE(Field02[Field])value Sel = SUMX(FILTER('DATA','DATA'[Field]=[Field01_Sel]),'DATA'[value])+SUMX(FILTER('DATA','DATA'[Field]=[Field02_Sel]),'DATA'[value])Ratio = DIVIDE(SUMX(FILTER('DATA','DATA'[Field]=[Field01_Sel]),'DATA'[value]),SUMX(FILTER('DATA','DATA'[Field]=[Field02_Sel]),'DATA'[value]))Step 2: I make two slicers and a matrix below.<Ratio=A/C><Ratio=E/A>
Thanks Mickey64.
Is it possible to move the Ratio under the value Sel ? The subtotal/Total prefers not shown. In addition, how to make the ratio create a line chart? Many thanks for your help!
Date 1/23/2026 2/23/2026 3/23/2026 Field Val Sel Val Sel Val Sel A 45 49 40 E 10 39 105 Ratio 0.222222 0.795918 2.625 I don't know how to move the Ratio under the value Sel.
It is easy making a line chart.
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Is this ok ?
Date Field value Jan-23 A 45 Jan-23 B 46 Jan-23 C 47 Jan-23 D 140 Jan-23 E 10 Feb-23 A 49 Feb-23 B 11 Feb-23 C 23 Feb-23 D 41 Feb-23 E 39 Mar-23 A 40 Mar-23 B 55 Mar-23 C 39 Mar-23 D 45 Mar-23 E 105 Example 2 from Slicer Jan-23 Feb-23 Mar-23 E 10 39 105 A 45 49 40 Ratio (E/A) 0.2222 0.7959 2.6250 from Slicer Jan-23 Feb-23 Mar-23 A 45 49 40 C 47 23 39 Ratio (A/C) 0.9574 2.1304 1.0256 Hi izzysoon
thanks. Anyway I would say mickey64 gave you a perfect answer.
Best
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI