Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
8 months ago
Solved

Wrong Total in Table

Hi all, I am getting worng total, I am tryint to calcualte the price of Power BI Licsenes cost for PPU, PRO and Free users using the following table and dynamically calculate the total which I am get...
Need Help
  • Suhel_Ansari's avatar
    Suhel_Ansari
    8 months ago

    Hi GeraldGEmerick  - I got the answer using the ADDCOLUMNS  however I have to use the multiple ADDCOLUMNS can you please help me to optimze this measure. Thanks 

     

cengizhanarslan's avatar
cengizhanarslan
Super User
8 months ago

Please try the following formula:

License Cost :=
VAR Lic   = SELECTEDVALUE ( 'Table'[License] )
VAR Users = DISTINCTCOUNT ( 'Table'[EmailID] )
VAR Price =
    SWITCH (
        Lic,
        "PBI_PREMIUM_PER_USER", 22.5,
        "Power BI Pro",         13.1,
        "Free Users",           0,
        0
    )
RETURN
IF (
    ISINSCOPE ( 'Table'[License] ),
    Users * Price,
    SUMX ( VALUES ( 'Table'[License] ), CALCULATE ( Users * Price ) )
)

 

 

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
8 months ago

hi cengizhanarslan  - this meaure returns 0 as the output as seen in the following screen print. Thanks 

 

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    8 months ago

    Seems like License values are not same as in the field useds in the visual and use use DISTINCTCOUNT insted COUNTA. If you used COUNTA to avoid nulls use DISTINCTCOUNTNOBLANK instead.