Forum Discussion

Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
4 years ago

Need help with the dax

My report requirement is as shown below:

For 1 particular tag, say 0009991 in this case, if the tag owner is brian and the order owner is also brian then my total procedure should be 1 and  % of usage should be 100%. And then when the tag owner is Abey and order owner is Bre in one case and Are in another case then total procedure should be (3+3=6) and % of usage should be 50% and 50%.

My measure for total procedure is

Total Procedures(Other Territory) =
SUMX(VALUES('Tag Usage Fact'[Tag ID]),CALCULATE(SUM('Procedure Fact'[Procedures]),ALLEXCEPT('Tag Usage Fact','Tag Usage Fact'[Tag ID])))

 

This measure is giving me the above result but I want it to be sorted by the tag id and the tag owner name at the same time.

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Avivek , Based on what I got try measure like

     


    divide(SUM('Procedure Fact'[Procedures]) =, CALCULATE(SUM('Procedure Fact'[Procedures]),ALLEXCEPT('Tag Usage Fact','Tag Usage Fact'[Tag ID], 'Tag Usage Fact'[Tag Name])))

     

    or

     

    divide(SUM('Procedure Fact'[Procedures]) =, CALCULATE(SUM('Procedure Fact'[Procedures]),filter(allselected('Tag Usage Fact'),'Tag Usage Fact'[Tag ID] = max('Tag Usage Fact'[Tag ID]) && 'Tag Usage Fact'[Tag Name] = max('Tag Usage Fact'[Tag Name]) )))

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating measures.

     

     

    Total Procedures measure: = 
VAR _currenttagID =
    MAX ( Data[Tag ID] )
VAR _currenttagowner =
    MAX ( Data[Tag Owner] )
VAR _result =
    CALCULATE (
        SUM ( Data[No of Procedures] ),
        FILTER (
            ALL ( Data ),
            Data[Tag ID] = _currenttagID
                && Data[Tag Owner] = _currenttagowner
        )
    )
RETURN
    IF ( HASONEVALUE ( Data[Tag Owner] ), _result )

     

    Percentage of usage measure: = 
DIVIDE( SUM(Data[No of Procedures]), [Total Procedures measure:] )