Forum Discussion
DAX help
Hi All,
I have created 2 DAX for the Win Rate % and Lost Rate %.
The Lost and Won are under the Outcome column. And the problem that I have here is I wanted to show the View of Won and Lost in the same tab.
I have used the Slicer for Outcome. But when I select the Won/Lost the below formula doesn’t work. Because it requires both Won and lost to calculate it.
Is there any way I can bring these 2 DAX into one(Or any other solution) and when I select Won it shows the won value and when I select Lost it shows the Lost value?
Won rate % = CALCULATE([Total Value selected Currency],FILTER('S&P','S&P'[Outcome]="Won"))/CALCULATE([Total Value selected Currency],FILTER('S&P','S&P'[Outcome]="Lost"||'S&P'[Outcome]="Won"))
Lost rate % = CALCULATE([Total Value selected Currency],FILTER('S&P','S&P'[Outcome]="Lost"))/CALCULATE([Total Value selected Currency],FILTER('S&P','S&P'[Outcome]="Lost"||'S&P'[Outcome]="Won"))
Sample data.
Win rate%=(550/1350)
Lost rate %=(800/1350)
| Outcome | Total Value selected Currency |
| Lost | 100 |
| Won | 200 |
| Lost | 300 |
| Lost | 400 |
| Won | 350 |
You can build a Slicer Table and build a measure to achieve your goal.
Slicer = VALUES('S&P'[Outcome])Measure:
M_Rate% = VAR _Selectoutcome = SELECTEDVALUE ( Slicer[Outcome] ) VAR _SumSelected = SUMX ( FILTER ( 'S&P', 'S&P'[Outcome] = _Selectoutcome ), 'S&P'[Total Value selected Currency] ) VAR _Total = SUMX ( ALL ( 'S&P' ), 'S&P'[Total Value selected Currency] ) VAR _Result = DIVIDE ( _SumSelected, _Total ) RETURN _ResultResult is as below.
Default:
Select Lost in Slicer:
Select Won in Slicer:
You can download the pbix file from this link: DAX help
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
RanjanThammaiah , one way is measure slicer
measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://www.youtube.com/watch?v=vlnx7QUVYMEthe second igonre won and lost in gt using all. For this better have result in sepratae table join that with this table take a all on that table
rate % = CALCULATE([Total Value selected Currency])/CALCULATE([Total Value selected Currency],FILTER(all('S&P'[Outcome]),'S&P'[Outcome] in{"Lost","Won" }))
rate % = CALCULATE([Total Value selected Currency])/CALCULATE([Total Value selected Currency],FILTER(all('Outcome'[Outcome]),'Outcome'[Outcome] in{"Lost","Won" }))
You can build a Slicer Table and build a measure to achieve your goal.
Slicer = VALUES('S&P'[Outcome])Measure:
M_Rate% = VAR _Selectoutcome = SELECTEDVALUE ( Slicer[Outcome] ) VAR _SumSelected = SUMX ( FILTER ( 'S&P', 'S&P'[Outcome] = _Selectoutcome ), 'S&P'[Total Value selected Currency] ) VAR _Total = SUMX ( ALL ( 'S&P' ), 'S&P'[Total Value selected Currency] ) VAR _Result = DIVIDE ( _SumSelected, _Total ) RETURN _ResultResult is as below.
Default:
Select Lost in Slicer:
Select Won in Slicer:
You can download the pbix file from this link: DAX help
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
