Forum Discussion

Tobz007's avatar
Tobz007
Frequent Visitor
1 year ago

KPI Cards for Monthly Variance (with DAX filter)

Hi need, some help here. So I have a matrix table that shows the difference (lets call it "Distance") between Odometer Reading between Current month and previous month. The matrix used Table[Name] and Table[Vehicle Number] as rows. There are 2 tables on the visual, one has a visual filter for Table2[Profile] = 1, the second has Table[Profile] = 2. Table 2 has a 1 to Many relationship with Table 1 using NAME_DATE_KEY.

The DAX works fine but the issue is creating a DAX for a KPI Card to show the percentage of Distance for Table2[Profile] = 1 and Table2[Profile] = 2, and to make it dynamic so that the result shown on the cards are correct when date slicer are used on when Table[Name] or Table[Vehicle Number] is selected on the matrix table. 

A couple of things to note is that its normal for some of the month difference to be negative especially if there was a mistake in current month input for Odometer or someone used 3 trucks last month and used used a truck this month.

NAME_KEYDateDATE_KEYVehicle NumberOdometer ReadingYearMonthNAME_DATE_KEYNameNAME_VEH_DATE_KEY
BATMAN01-Aug-2480120243534110,676Aug-24BATMAN2024-08Bat ManBATMAN2752412024-08
BATMAN01-Jul-2470120243534116,446Jul-24BATMAN2024-07Bat ManBATMAN2752412024-07
BATMAN01-Jun-2460120243534111,079Jun-24BATMAN2024-06Bat ManBATMAN2752412024-06
BATMAN01-May-24501202435341140,078May-24BATMAN2024-05Bat ManBATMAN2752412024-05
BATMAN01-Apr-24401202435341140,078Apr-24BATMAN2024-04Bat ManBATMAN2752412024-04
BATMAN01-Apr-2440120243543RD4,634Apr-24BATMAN2024-04Bat ManBATMAN2754212024-04
BATMAN01-Mar-24301202435341135,262Mar-24BATMAN2024-03Bat ManBATMAN2752412024-03
BATMAN01-Feb-24201202435341126,447Feb-24BATMAN2024-02Bat ManBATMAN2752412024-02
BATMAN01-Jan-24101202435114169,965Jan-24BATMAN2024-01Bat ManBATMAN2751142024-01
SPIDERMAN01-Dec-231201202335341110,734Dec-23SPIDERMAN2023-12Spider ManSPIDERMAN2752412023-12
SPIDERMAN01-Nov-231101202335341104,795Nov-23SPIDERMAN2023-11Spider ManSPIDERMAN2752412023-11
SPIDERMAN01-Oct-23100120233534199,537Oct-23SPIDERMAN2023-10Spider ManSPIDERMAN2752412023-10
SPIDERMAN01-Oct-231001202325614,763Oct-23SPIDERMAN2023-10Spider ManSPIDERMAN2752412023-10
SPIDERMAN01-Oct-23100120233218914,351Oct-23SPIDERMAN2023-10Spider ManSPIDERMAN2752412023-10
SPIDERMAN01-Sep-2390120233534193,313Sep-23SPIDERMAN2023-09Spider ManSPIDERMAN2752412023-09
SPIDERMAN01-Aug-23801202335341 Aug-23SPIDERMAN2023-08Spider ManSPIDERMAN2752412023-08
SPIDERMAN01-Jul-2370120233534182,759Jul-23SPIDERMAN2023-07Spider ManSPIDERMAN2752412023-07
SPIDERMAN01-Jun-2360120233534180,755Jun-23SPIDERMAN2023-06Spider ManSPIDERMAN2752412023-06
SPIDERMAN01-May-2350120233534176,916May-23SPIDERMAN2023-05Spider ManSPIDERMAN2752412023-05
SPIDERMAN01-Apr-2340120233534172,246Apr-23SPIDERMAN2023-04Spider ManSPIDERMAN2752412023-04
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Tobz007 ,

    Based on the description, please try the following methods:

    1.Create the new measure to calculate monthly distance.

    Monthly Distance = 
VAR CurrentReading = SUM(Table1[Odometer Reading]) 
VAR PreviousReading = CALCULATE(SUM(Table1[Odometer Reading]), PREVIOUSMONTH(Table1[Date]))
RETURN 
IF(NOT ISBLANK(PreviousReading), CurrentReading - PreviousReading)

    2.Create the new measure to filter the month.

    Total Distance Profile 1 = CALCULATE( SUMX(Table1, [Monthly Distance]), Table2[Profile] = 1 )

    3.Create the new measure to calculate the percentage.

    Percentage Prof 1 = DIVIDE( [Total Distance Profile 1], CALCULATE(SUMX(Table1, [Monthly Distance])))

    Besides, Is the example data above from Table 1 or Table 2? And, there is no profile column inside the table data. Can you provide the complete table data?

     

    Best Regards,

    Wisdom Wu

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