Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

DAX Calculation - Scaling Value based on Sum(Value) in same Column

Hi,

 

I have a dataset containing cars with several measurements (example: number of honks). The honks are measured at different times and then summed. I now want to scale the number of honks depending on how far a car has driven. 

 

Example: 

CAR 1: 50 honks - 100 km

CAR 2: 20 honks - 200 km

If we now scale this to 100km, we would get 50 honks for the first and 10 honks for the second car. (Number of honks/Number of kms)*100

 

How can I perform this calculation in PBI? I have tried it using CALCULATE and FILTER (see below), but am getting invalid results.

Scaled_value_calc = sum(Sheet1[Value])/CALCULATE(sum(Sheet1[Value]), FILTER(Sheet1, Sheet1[Measurement_Category] = "Kilometers"))*Parameter[Parameter Value]
 

Link to workbook with sample data: https://drive.google.com/drive/folders/1t0aGYeRQD-t4Z3im5pQE06CIKa3xoKU1?usp=drive_link

NOTE: The column Scaled_Value is the value I am trying to calculate. I mocked it up in Excel to provide a better explanaition of my problem. The real data set does not have that column. 

Thanks for your help!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Could not access the sample data that you share, i create a sample for you with my understand.

    Please try below steps:

    1. below is my test table

    Table:

    2. create a measure with below dax formula

    Measure =
VAR _a =
    SELECTEDVALUE ( 'Table'[Car Name] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [Car Name] = _a )
VAR tmp2 =
    SELECTCOLUMNS ( tmp1, "Honks", [Honks] )
VAR tmp3 =
    SELECTCOLUMNS ( tmp1, "KMS", [KMS] )
VAR _b =
    SUMX ( tmp2, [Honks] )
VAR _c =
    SUMX ( tmp3, [KMS] )
RETURN
    DIVIDE ( _b, _c ) * 100

     

    3. add a table visual with table fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous,

     

    thank you for your reply. You almost got my problem but my issue is that the kilometer and the honks values are in the same column and only distinguished by another column measurement_category. 

    I am sorry I am unable to attach the PBIX file here for some reason. I have this screenshot instead:

    The value for the cell marked in yellow should be 0,96. As mentioned above, the Scaled_Value column is mocked up in Excel and not available in the real data set.

     

    Thanks for your help!