How to get value divide by total and not row

[Anonymous]

Hi all,

Any idea how I can get the result as my "required output" below?

For the "result", I use quick measure formula : sum(abnormality)/sum(usage).

*Current table I already unpivot the start(unit) & joint(unit).

Regards,

Nuha

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

Usage measure: = 
SUMX (
    SUMMARIZE (
        CALCULATETABLE ( Data, ALL ( Data[Abnormality] ) ),
        Data[Day],
        Data[Usage]
    ),
    Data[Usage]
)

[Anonymous]

Hi @Jihwan_Kim ,

This is fantastic method but there will be a problem when there are two or more production for the same day as below example. The usage measure should sum both of the row quantity but it pick 1 value row only.

Current measure formula

Day	Abnormality	Value	Usage Measure
19	Joint	86	8869

Actual Data

Day	Abnormality	Value	Usage Measure
19	Joint	0	7293
19	Joint	86	8869

Below are the formula I use as per your advice

Usage measure =

SUMX(

    SUMMARIZE (

        CALCULATETABLE('For Graph',ALL('For Graph'[Abnormality])),

        'For Graph'[Day],

        'For Graph'[SAP_PPT_EV_MaterialDocument_GI (2).Total GI Coating]

    ),

    'For Graph'[SAP_PPT_EV_MaterialDocument_GI (2).Total GI Coating]

)

Regards,

Nuha

[Anonymous]

Hi @Jihwan_Kim ,

For the data model, I already replied the comment below this thread for your reference.

Regards,

Nuha

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[Anonymous]

Current Data List

Day	Abnormality	Value	Usage
26	Start	442	35343
22	Joint	215	41217
26	Joint	146	35343

Current Powerbi Formulation

Abnormality	Start
Value	442
Usage	35343
Result (Value/Usage)	1.25%

Abnormality	Joint
Value	361
Usage	76560
Result (Value/Usage)	0.48%

Required Output

Abnormality	Start
Value	442
Usage	76560
Result (Value/Usage)	0.58%

Abnormality	Joint
Value	361
Usage	76560
Result (Value/Usage)	0.48%

Regards,

Nuha