cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Super User

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 3
Super User

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.

Frequent Visitor

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.

Super User

Great. But I already included FORMAT in my formula