Forum Discussion

atult's avatar
atult
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Different value at granular level and aggregated level

Hello experts,
We have data at manufacturer and keyword level. I require Share of search(SOS) and weighted share of search(Wtd SOS) which i have successfully calculated.

Where I’m facing issue is i need to show the calculated Wtd SOS at keyword level whereas need to show SUM(Wtd SOS) / SUM(Weights) at aggregated level.

Can someone please help me with the DAX so that i can achieve the above in a single calculated measure?

 

Data and Expected output (Light green cells at keyword level whereas dark green cell value in aggregated view in card/bar/line):

 

Data in table:

ManufacturerKeywordValueWeights
MW1abc100.01
MW1xyz200.02
MW2pqr300.03
MW2def400.07

 

amitchandak , lbendlin , Ritaf1983 , Ahmedx , Idrissshatila , parry2k 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi atult ,

    Please try to create measure with below dax formula:

    Measure =
VAR _a =
    SELECTEDVALUE ( 'Table'[Weights] )
VAR _b =
    SELECTEDVALUE ( 'Table'[SOS] )
VAR _c = _a * _b
RETURN
    _c

    Measure2 =
VAR _a =
    SUMX ( 'Table', [Weights] )
VAR _b =
    SUMX ( 'Table', [Measure] )
RETURN
    IF ( ISINSCOPE ( 'Table'[Manufacturer] ), _b, DIVIDE ( _b, _a ) )

     

    Add a table visual with 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.

4 Replies