Forum Discussion
How to Calculate Win (%) for Matrix Table
I want to include the Win (%) in my Matrix table but I can't seem to figure it out. Do I need to include a Win (%) row in my data? or is there another way to dot it with DAX?
| Win | Win | Loss | Loss | Win (%) | Win (%) | |
| Person | Current Month | Year to Month | Current Month | Year to Month | Current Month | Year to Month |
| A | 1000 | 3000 | 3000 | 6500 | 25% | 32% |
| B | 3000 | 6500 | 4000 | 9000 | 43% | 42% |
Current Month = CALCULATE( SUM( [Value] ), [Month] = MAX( [Slicer Month] ) )
Year to Month = CALCULATE( SUM( [Value] ), [Month] <= MAX( [Slicer Month] ) && [Month] >= MIN( [Slicer Month] ) )
Data for the matrix is derived from 2 tables using UNION(SELECTCOLUMNS(TABLE 1 Columns),SELECTCOLUMNS(TABLE 2 Columns))
| Person | Month | Category | Value |
| A | Apr | Win | 1000 |
| A | Apr | Loss | 1500 |
| B | Apr | Win | 1500 |
| B | Apr | Loss | 2000 |
| A | May | Win | 1000 |
| A | May | Loss | 2000 |
| B | May | Win | 2000 |
| B | May | Loss | 3000 |
| A | Jun | Win | 1000 |
| A | Jun | Loss | 3000 |
| B | Jun | Win | 3000 |
| B | Jun | Loss | 4000 |
from the format pane of the visual activate the columns total. Then adust your measures as folows:
Current Month = VAR CurrentMonth = CALCULATE ( SUM ( [Value] ), [Month] = MAX ( [Slicer Month] ) ) VAR YeartoMonth = CALCULATE ( SUM ( [Value] ), [Month] <= MAX ( [Slicer Month] ) && [Month] >= MIN ( [Slicer Month] ) ) RETURN IF ( HASONEVALUE ( [Category] ), CurrentMonth, FORMAT ( DIVIDE ( CurrentMonth, YeartoMonth ), "Percent" ) )Year to Month = VAR CurrentMonth = CALCULATE ( SUM ( [Value] ), [Month] = MAX ( [Slicer Month] ) ) VAR YeartoMonth = CALCULATE ( SUM ( [Value] ), [Month] <= MAX ( [Slicer Month] ) && [Month] >= MIN ( [Slicer Month] ) ) RETURN IF ( HASONEVALUE ( [Category] ), YeartoMonth, FORMAT ( DIVIDE ( CurrentMonth, YeartoMonth ), "Percent" ) )However, not sure how you calculated the percentage so you might need to adjust based on your requirement.
3 Replies
from the format pane of the visual activate the columns total. Then adust your measures as folows:
Current Month = VAR CurrentMonth = CALCULATE ( SUM ( [Value] ), [Month] = MAX ( [Slicer Month] ) ) VAR YeartoMonth = CALCULATE ( SUM ( [Value] ), [Month] <= MAX ( [Slicer Month] ) && [Month] >= MIN ( [Slicer Month] ) ) RETURN IF ( HASONEVALUE ( [Category] ), CurrentMonth, FORMAT ( DIVIDE ( CurrentMonth, YeartoMonth ), "Percent" ) )Year to Month = VAR CurrentMonth = CALCULATE ( SUM ( [Value] ), [Month] = MAX ( [Slicer Month] ) ) VAR YeartoMonth = CALCULATE ( SUM ( [Value] ), [Month] <= MAX ( [Slicer Month] ) && [Month] >= MIN ( [Slicer Month] ) ) RETURN IF ( HASONEVALUE ( [Category] ), YeartoMonth, FORMAT ( DIVIDE ( CurrentMonth, YeartoMonth ), "Percent" ) )However, not sure how you calculated the percentage so you might need to adjust based on your requirement.
Thank you very much, my actual data is bit different but this leads me to what I want. I just added FORMAT () to RETURN percentage instead of decimal.
Great. But I already included FORMAT in my formula
