topic Re: Ratio Calculation in DAX Commands and Tips

Ratio Calculation

Anonymous — Thu, 22 Feb 2024 04:12:57 GMT

Hello, I need some assistance here. I have below sample data. I need to find out the ratio between two status of the issue.

Needed output is ration between the types of issue (but only between Parts and Hardware). In this case it is 1:2

Somebody can please assist me? So that I can get ration for each site.

Sl No	Location	Type Of Issue	Date Reported	Status
1	Site 1	Hardware	10-Jan-24	Resolved
2	Site 2	Software	10-Jan-24	In Progress
3	Site 3	Software	10-Jan-24	Resolved
4	Site 2	Parts	15-Jan-24	Awaiting
5	Site 2	Hardware	15-Jan-24	Resolved
6	Site 3	Parts	31-Jan-24	Resolved
7	Site 7	Hardware	2-Feb-24	Resolved
8	Site 8	Hardware	2-Feb-24	Awaiting
9	Site 7	Software	20-Feb-24	In Progress
10	Site 10	Software	20-Feb-24	Resolved

Re: Ratio Calculation

Jihwan_Kim — Thu, 22 Feb 2024 04:50:49 GMT

Hi,

I am not sure about how your semantic mode looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

KEEPFILTERS function (DAX) - DAX | Microsoft Learn

Issue count % only hardware + parts: = VAR _countall = COUNTROWS ( CALCULATETABLE ( Data, 'Type Of Issue'[Type Of Issue] IN { "Hardware", "Parts" } ) ) VAR _count = COUNTROWS ( CALCULATETABLE ( Data, KEEPFILTERS ( 'Type Of Issue'[Type Of Issue] IN { "Hardware", "Parts" } ) ) ) RETURN DIVIDE ( _count, _countall )

Re: Ratio Calculation

ThxAlot — Thu, 22 Feb 2024 09:16:57 GMT

Re: Ratio Calculation

Anonymous — Thu, 22 Feb 2024 10:33:37 GMT

Thank You ThxAlot and Jihwan_Kim

Bu am looking in a ration form. Example as below.

Thank You ThxAlot Jihwan_Kim

But am looking in a ration form.

Example as below.

Site	Hardware	Parts	Ratio
Site 1	1		1:0
Site 2	2	1	1:1
Site 3	5	15	1:3

Re: Ratio Calculation

Anonymous — Fri, 23 Feb 2024 05:20:43 GMT

Hi Anonymous ,

Based on your problems, here are my answers.

First I created a table based on your description.

Then created four Measure.

Total Hardware Issues = SUMX(FILTER('Table', 'Table'[Type Of Issue] = "Hardware"), 1)

Total Parts Issues = SUMX(FILTER('Table', 'Table'[Type Of Issue] = "Parts"), 1)

Issue Ratio = DIVIDE([Total Hardware Issues], [Total Parts Issues])

Ratio Display = (CONCATENATE("1:", [Issue Ratio]))

Finally you will see the result you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Ratio Calculation

Anonymous — Wed, 28 Feb 2024 10:49:46 GMT

Anonymous Thank you for the response and udpate. Apreciate it. It works. Cool.

I havew one more thing. What if we have more decimal values in the outout. I tried to chnage the decimal value to zero, but it wont change in the result.

Re: Ratio Calculation

Anonymous — Wed, 06 Mar 2024 08:58:38 GMT

Anonymous

Is it possible to remove the decimal points?